Level 3: Debt and cash · Exercise 39 of 74

Annual rate to a rate per period

A financial modeling exercise from Modeldrill. Solve it with a live spreadsheet grid in your browser, with Fill right and Fill down, and it is checked the moment you submit.

The task

The rate in B2 is an effective annual rate. For each period length fill the equivalent compound rate per period (column C: one plus the annual rate, raised to the power of 1 over the periods per year, minus 1), the simple rate that divides the annual rate by the number of periods (column D), and the annual rate that simple rate really amounts to once it compounds (column E).

What this exercise teaches

Dividing an effective annual rate by 12 overstates the monthly rate: 8% a year is 0.64% a month, not 0.67%, and 0.67% a month compounds to 8.3%. Division is only right when the rate is quoted as a nominal rate, the way loan rates usually are.

A hint if you get stuck

Compound: (1 + annual rate)^(1 / periods) - 1. Simple: annual rate / periods. To annualize a rate per period: (1 + rate per period)^periods - 1.