About this lesson
EFFECT and NOMINAL in Google Sheets: comparing rates that compound differently
In Google Sheets, EFFECT turns a quoted (nominal) annual rate into the effective annual rate after compounding: =EFFECT(B2,C2), the same as =(1+B2/C2)^C2-1. NOMINAL goes the other way: =NOMINAL(effective,periods per year). Compare quotes that compound differently on their effective rates, never their quoted ones.
Using Excel? Do this lesson in Excel.
=EFFECT(B2,C2)What you'll practise
Compare three banks' quotes for a capex facility that compound quarterly, monthly and annually: convert each to an effective annual rate, find the cheapest, check one by hand, then convert back with NOMINAL.
4 steps · 40 points · the cells outlined in gold below
- EFFECT
- NOMINAL
- effective annual rate
- compounding frequency
The data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Quote | Quoted rate | Times a year | Effective rate |
| 2 | Bank A | 6.80% | 4 | |
| 3 | Bank B | 6.65% | 12 | |
| 4 | Bank C | 6.88% | 1 | |
| 5 | ||||
| 6 | Lowest effective rate | |||
| 7 | Bank B by hand | |||
| 8 | Bank A, monthly |