About this lesson
RATE and NPER: the rate inside a lease, how long it takes to repay, and checking it with Goal Seek
RATE finds the interest rate behind a loan or lease when you know the payment: =RATE(periods,-payment,amount) gives the rate per period, so multiply by 4 for quarterly payments. NPER finds how many payments it takes: =NPER(rate,-payment,amount). To check either, put the rate in a cell, build the PMT on it, and use Data, What-If Analysis, Goal Seek to set the PMT to the quoted payment.
=RATE(B3,-B2,B1)*4What you'll practise
Work out the interest rate hidden in an equipment lease quote with RATE, how many quarters a bigger payment takes to clear it with NPER, then prove the rate with Goal Seek on a PMT.
4 steps · 40 points · the cells outlined in gold below
- RATE
- NPER
- Goal Seek
- implied interest rate
- lease analysis
The data
| A | B | |
|---|---|---|
| 1 | Equipment cost ($) | $2,850,000.00 |
| 2 | Quarterly payment ($) | $164,500.00 |
| 3 | Term (quarters) | 20 |
| 4 | ||
| 5 | Implied annual rate | |
| 6 | Bigger payment ($) | $230,000.00 |
| 7 | Quarters to repay | |
| 8 | ||
| 9 | Check rate (a year) | 5.00% |
| 10 | Payment at check rate ($) |