Description
Prepare an Excel model that will produce a lease amortization table as follows:
- The net present value of a ten-year payment stream of $10,000 per month at 3% (assume the first payment is 30 days away)
- An amortization table of the payment stream showing the allocation of each monthly payment to interest and principal
Make sure the following input variables can be changed:
- Monthly payment amount
- Interest rate
USING EXCEL TO CALCULATE NPV
Things to remember:
- Excel assumes the first payment is one period away
- Formula =NPV(RATE,Cell_Range) =NPV(4%,A1 A12)
- If the first payment (Cell A1) is received on day 1, the formula should be: =A1+NPV(4%,A2 A12)
- The rate must agree to the period of cash flows. Monthly payments using an annual rate of 4% should be shown as follows: =NPV((4%/12),A1 .A12)