1

Trying to develop a budget model in Excel. I know a prospective savings account makes 4% per annum, compounded semiannual. I'd like to know how much this means it earns monthly. Does it even earn anything monthly if the compounding is said to be on a semi-annual basis?

  • Keep it simple and just divide by 12. You'll be off by a fraction of a percent (it's really 0.3275% instead 0.3333%, a difference of 58 cents on $10,000). – RonJohn Aug 24 '18 at 21:35
  • Ron I agree with you 100%. Trying to get a high level of precision on something that has higher variability makes little sense. – JTP - Apologise to Monica Aug 24 '18 at 23:23
1

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...

  • 4% APR compounded semi-annually is 1.9804% semiannually, not 2% semi-annually. – RonJohn Aug 24 '18 at 21:30
  • 2
    Where does the OP use the term APR? – DJohnM Aug 24 '18 at 22:36
  • @RonJohn The phrase “compounded semi-annually” means it is a nominal rate, so the semi-annual rate is 4%/2 = 2%. Please see en.m.wikipedia.org/wiki/Effective_interest_rate#Calculation , particularly this line : “6% compounded monthly is credited as 6%/12 = 0.005 every month.” – Chris Degnen Aug 25 '18 at 9:14
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.