Create two amortization tables on a separate worksheet (not file).

Name each worksheet Ord ann Ann due.

assume the following:

a. 10 year lease

b. payments of 5,000 happen quarterly

c. Annual market rate of interest is 6%

d. 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 (in other words the BPO)

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 don’t 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.

Answer rating: 100% (QA)

Answer An amortization lease table is a schedule that depicts total number of payments to be paid du... View the full answer