For the excel lease assignment, I would like for you to complete two amortization tables.

Follow these instructions (meant to make sure you can use excel).

Create each table on a separate worksheet (not file).

Name each worksheet Ord ann Ann due.

In this assignment, the table you are to prepare in excel should end at zero because you have to include the bargain purchase option (BPO) in the table as your final cash flow. Depending on whether you have an ordinary annuity or annuity due, the BPO may be included with the last lease payment. In either type of annuity, the BPO will be either part of the final payment with the last least payment or the final payment alone.

assume the following:

1. 10 year lease
2. payments of 5,000 happen quarterly
3. Annual market rate of interest is 6%
4. There is a bargain purchase option (BPO) of $30,000

The first 5 lines of the worksheet should have the following information with labels in column A and the numerical value (or formula to calculate the numerical value in column B.

Number of compounding periods

Principal amount

Interest Rate (adjusted to periodic amount)

Quarterly payment

Calculation of the pv of the annuity and the BPO. You can do this in two formulas or one, your choice. If you use two, add these together.

The table shall contain 5 columns beginning after row 5.

Col 1: Beginning Book Value

Col 2: Interest Expense

Col 3: Cash flow or payment

Col 4: Reduction in Principal

Col 5: Ending book value

There should be nothing in the worksheet except formulas which will reference back to one of the calculations or inputs in rows 1-5. For example, your calculation of interest expense in the table will be a formula that references back to the cell address where you entered principal amount and the relevant interest rate. Using an absolute reference will make this easier for some of the work. Look that up in the help function if you dont know what that means.

I will grade this by changing any value in rows 1-5. To get full credit, the entire worksheet should update accurately using the new values input in rows 1-5. Thus, the worksheet should work for any value in rows 1-5.

For the worksheet Ord ann, you assume that the cash flow occurs at the end of each compounding period.

For the worksheet Ann Due, you assume that the cash flow occurs at the beginning of each compounding period.

The example from class was an annuity due.

You can create a table for the annuity due, copy the work to the second worksheet and modify it some to be applicable for an ordinary annuity.

Your last row, last column should equal to zero if you have done the table correctly.