Back into the interest rate a financing offer never quotes
RATE works the loan formula backward — from a known payment to the rate that produces it.
Task:
You handle purchasing at a mid-size manufacturing company evaluating a lease-to-own offer on a warehouse forklift. The equipment dealer will finance $18,500, repayable in 60 monthly installments of $365 — but the offer sheet never states an interest rate, and procurement wants one before they can compare it against the company's bank line at 8% APR. Work out the rate the dealer is actually charging: the monthly rate in B5, and the annual rate it implies in B6.
Learning Objectives:
- Use RATE to back into an interest rate from a known payment and term
- Recognize when a financial question runs backward from PMT and FV rather than forward
- Keep pv and pmt on opposite signs, the same convention PMT and FV use
- Convert a rate per period into the annual rate it's usually quoted as
Solve without hints for +5 XP
Interactive Spreadsheet
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Loan amount | 18500 |
| 3 | Monthly payment | 365 |
| 4 | Term (months) | 60 |
| 5 | Monthly interest rate | |
| 6 | Annual interest rate (APR) |
What this exercise teaches (contains the answer)
PMT and FV start from a rate and work out a payment or a balance; RATE runs that arithmetic in reverse, iterating toward whichever rate makes the loan amount, the payment, and the term agree with each other — Excel does this by successive approximation rather than solving for it directly, because there's no algebraic rearrangement that isolates rate on one side. The sign convention matters here for the same reason it does in PMT: the $18,500 arrives once, so it's entered as a positive pv, and the $365 leaves every month after, so it's entered as a negative pmt — reverse those and RATE returns a rate with the wrong sign, or no rate at all. B5 comes out to a shade under 0.6% a month, which sounds harmless until B6 multiplies it out to just under 6.85% APR — comfortably under the 8% bank line, and a number the dealer's offer sheet never once printed.