A finance quote that says "5% flat" sounds cheaper than one that says 7.9% a year, but a flat rate charges interest on the whole amount for the whole term, even after most of it has been paid back. RATE works backwards from the payment to the rate you are really paying, so two quotes written in different languages can be compared on the same footing.
You manage the finances of a print shop that is financing a $15,000 wide-format printer over 4 years. Two lenders have quoted, and their offers are in B2:C5: Northgate Finance at 5% flat and Harbour Bank at 7.9% a year, both repaid monthly. In B6, use RATE to work out the true annual interest rate of Northgate's quote, and copy it across to C6 for Harbour's. Then in B7, work out the total interest each quote costs, and copy that across to C7.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 | C | |
|---|---|---|---|
| 1 | Northgate Finance | Harbour Bank | |
| 2 | Amount financed | 15000 | 15000 |
| 3 | Monthly payment | 375 | 365.49 |
| 4 | Term (months) | 48 | 48 |
| 5 | Quoted rate | 5% flat | 7.9% a year |
| 6 | True annual rate | ||
| 7 | Total interest |
A flat rate is worked out once, on the full amount borrowed: 5% of $15,000 is $750 a year, $3,000 over 4 years, spread across 48 payments of $375. But each payment repays part of the loan, so by the last year the shop owes less than a quarter of the original amount and is still being charged interest as if it owed all of it. RATE ignores how the rate was described and asks the only question that matters: what monthly rate makes 48 payments of $375 exactly repay $15,000? =RATE(B4,-B3,B2) solves that, the payment going in negative because it flows the opposite way to the loan, and multiplying by 12 gives the annual figure lenders quote, 9.24% for Northgate. The same formula on Harbour's numbers returns 7.90%, matching what Harbour advertised, because a yearly rate already charges interest only on what is still owed. So the quote that looked cheaper is the dearer one: row 7 shows Northgate costing $3,000 in interest against Harbour's $2,543.52, a difference of $456.48 for the same printer over the same term. Leaving off the *12 is the slip to watch for: it returns 0.77% and 0.66%, monthly rates that look like a bargain and compare with nothing either lender actually quoted.