: How do I convert a 4% per annum interest rate, compounded semi-annually, to a monthly rate? Trying to develop a budget model in Excel. I know a prospective savings account makes 4% per annum, compounded semiannual. I'd like

Vote Up Vote Down   0 Reactions

Login to give Reaction!

For this type of problem, it is often easier to convert from one rate to another through a third standard interest rate. One good candidate for this intermediate rate is what, here in Canada, is called the effective annual rate. So here goes:

If you earn 4% per year, compounded semi-annually, then you earn 2% over the first half-year. Then the earned interest is credited, and you earn 2% on this larger amount over the next half-year.

So, you wind up with (1.02)*(1.02), or 1.0404, for each unit invested. The effective annual rate is 4.04%. If you invested the unit at 4.04% compounded annually, you would wind up with the same amount earned.

Now, how to get back down to monthly compounding.

Suppose your monthly compounded investment grows by a factor of x each month. Then, after 12 months, you'll have xxxxxxxxxxx*x, or x^12

So you want to find x such that x^12 = 1.0404.

Using the root button on a scientific calculator to take the 12th root of 1.0404, we get x = 1.00330589

So the monthly rate is 0.330589% per month.

Finally, multiplying this monthly rate by 12, we find that 4% per year compounded semi-annually, is the same as 3.967068% per year, compounded monthly...

Vote Up Vote Down   0 Reactions

Login to give Reaction!

This answer addresses your stated objective, "Trying to develop a budget model in Excel", rather than the headline question about converting between different compounding interest-rate scenarios.

Given a simple interest rate of 4%pa, you will get

no interest in your account for the first 5 months, then
2% in your 6th month,

after which the cycle repeats.

What the 2% is calculated on depends on the terms and conditions related to your account. Unlike term deposits, which lock up the capital for an agreed period, savings accounts usually allow you to deposit and/or withdraw money at any time. The semi-annual interest might be calculated by accruing simple interest of 4%pa on each day's minimum balance. You don't get access to this interest until the end of each 6-month period. If they calculate based on minimum monthly balances or by using average balances, you'll end up with a different figure for the 2% each 6 months.

Assuming a 12-month budget:

include the 2% figure twice a year based on your forecast of the account balance and the terms of your account;
use a zero figure for that account in the remaining 10 months of the year. Interest may be accruing internally but you won't see it in your bank statements for those months.