solution

A B C D E

1 Schedule of Lease Payments

2 (Amortization Schedule)

3 Five-Year Lease–Beginning-of-Year Payments

4 Annual Lease Payment $60,000

5 Bargain Purchase Option Amount or

6 Excess Guaranteed Residual Value $75,000

7 Interest Rate for the Lease 10%

8 Life of the Lease (# of annual payments) 5

9 Initial Lease Obligation (Payable) $296,761.03

10 Date Amount Interest Expense Principal Lease Obligation

11 Initial Balance $296,761.03

12 Beg of Year 1 $60,000 0 $60,000 $236,761.03

13 Beg of Year 2 $60,000 $23,676.10 $36,324 $200,437.13

14 Beg of Year 3 $60,000 $20,043.71 $39,956 $160,480.84

15 Beg of Year 4 $60,000 $16,048.08 $43,952 $116,528.93

16 Beg of Year 5 $60,000 $11,652.89 $48,347 $68,181.82

17 End of Year 5 $75,000 $6,818.18 $68,182 $0.00

Your assignment is to prepare a spreadsheet similar to the example given (with the same numbers). This particularspreadsheet is set up to provide a schedule of lease payments (amortization schedule) for a five-period lease withpayments made once each year at the beginning of the year with the option of including a bargain purchase optionamount or excess guaranteed residual value. Your task is to make the spreadsheet general enough that I could inputany new numbers for the annual lease payment, bargain purchase option amount or excess guaranteed residual value,and interest rate (cells E5, E7, and E8 in the example) and automatically have an updated amortization schedule forany five-period capital lease with payments at the beginning of the period. In your spreadsheet, all of your numberscorresponding with the shaded cells in my example should be the result of formulas referring to other cells in thespreadsheet, NOT the result of simply typing numbers into your spreadsheet cells (but you do not need to shade yourcells).

You are not required to use Excel, but I will give some help assuming that you use Excel. If you use anotherspreadsheet program, you will need to make sure you understand how to accomplish the requirements in thatprogram. The initial lease obligation (cell E11 in the example) should be a present value formula (PV inExcel–many of you will use the Insert Function command under Formulas) which calculates the present value of theliability based on the numbers in the cells above that (cells E5, E7, E8, and E9 in the example). The arguments ofthe present value formula can/should be cell addresses (except the last “1″ to indicate beginning-of-year payments). Your initial lease obligation may come out negative by using the syntax given, so add a “-“ sign to your formula sothat the lease obligation on your spreadsheet will be a positive number.

You need to submit two pages for your solution–one page will have the spreadsheet with the numbers showing andthe other will have the spreadsheet with the formulas showing (You may need to adjust the column size of somecolumns to make sure all formulas are printed out completely. Since you are turning in formulas, I will not needyour file for grading). DO NOT submit the Excel file. Instead “print” each page to PDF format and submit thosePDFs. That way I can verify that you followed the instructions below. I will also ask for you to use the followingspecific format when you “print” your results:

1.Each printout should have the row and column headings (A, B, C; 1, 2, 3, etc.)EXCEL:Print Preview/Page Setup/Sheet/Row and Column Headings (check this item)

2.Each printout should have the gridlines printed.EXCEL:Print Preview/Page Setup/Sheet/Gridlines (check this item)

3.Each printout should be formatted to fit on one page.EXCEL:Print Preview/Page Setup/Page/Fit to (check this item and put 1 wide x 1 tall)

4.Each printout should be formatted using either the portrait or landscape format, whichever fits better for thatparticular printout (you may need to change your choice here when you print out your formulas).

5.To print the format with the formulas instead of numbers use the following procedure in Excel (or useCTRL ~ to toggle back and forth from numbers to formulas):EXCEL:Formulas/Formula Auditing/Show Formulas (check this item)

I want each student to complete the project individually. However, you may help each other with the concepts of theproblem and the spreadsheet skills as long as you are not doing the work for each other. Make sure any help yougive is assisting others to learn (either spreadsheet or accounting skills) rather than just helping them to complete theassignment. The purpose of this project is to make sure each student understands how to use a spreadsheet programand how to perform a specific application related to this class. I don’t want this experience to be extremelyfrustrating. Therefore, ask me if you need help. Start early so you have time to get help if you need it. I suspectsome of you will find this assignment very easy. Others may struggle and need some help, especially those whodon’t have much experience with spreadsheets. Also, include the following on your spreadsheet so they will beincluded on each page you “print”/submit: your name and your statement that you completed the assignment byyourself (or with appropriate help). The assignment (both “printouts”) is due in Canvas on Thursday, October 28,2021, by the end of your class time.