Log In
Register
Home
Topic RSS
Facebook
Twitter
Email this8:54 am
February 27, 2018
Offline
maybe we could start a new tab dealing with financial calculations. i scoured the net yesterday, looking for a rrif gic calculation and then i just sat down and made my own. it's a very crude spreadsheet, there must be a better way to do this?
the values i used in the excel picture are for display purposes only. i highlighted the repeat calculation used throughout the sheet, showing how the amount of $2,000 a month is fixed $C$8, and how the interest rate is also fixed at 3% $C$9.
Loonie, i hope this picture is clear.
** I went for a walk last night and my kids asked me how long I'd be gone. I said, "The whole time."
4:20 pm
October 21, 2013
Online
Thanks for considering my needs and abilities.
I can read the numbers OK, but I am baffled by them.
5:28 pm
February 27, 2018
Offline
After hearing Friday's inverted yield curve news, i decided to lock my rrif into a gic and the banks never show you their math. an asset being reduced while making interest is a tricky calculation.
The formula i used. (principal "A" - withdrawal amount) x interest rate % / 365 days x number of days in that month. Then to that you must add (principal "A" - withdrawal amount) this will give you principal "B" (which is the principal amount used in next month's calculation).
So the second month would be
(principal "B" - withdrawal amount) x interest rate % / 365 days x number of days in that month. Then to that you must add (principal "B" - withdrawal amount) this will give you principal "C" (which is the principal amount used in next month's calculation).
The only tweak i've made so far is. I caught that February 2020 has 29 days. I missed, 2020 has 366 days.
The question becomes... what if my end results differ from the banks?
6:17 pm
April 6, 2013
Offline
The calculations shown imply monthly compounding of interest. That may not be the case with an RRIF GIC.
I checked the terms and conditions of the RRIF's at Oaken and Hubert. Both mention annual compounding of interest and not monthly.
Annual compounding means that the monthly payments are 100% principal, except for the monthly payments on, or immediately after, the anniversary dates, when the interest is compounded.
6:44 pm
February 27, 2018
Offline
Yes, Norman you are correct. Reading the fine print, it states, interest is paid annually and compounded. It says nothing about... calculated monthly, on the minimum balance as i have spent my time figuring out.
If... my interest is only being calculated once a year either on December 31st, or on the anniversary date makes no sense. I was getting 2.5% calculated monthly on the minimum balance when it was just sitting in savings. A multiple year gic calculated once a year would need to pay 4 or 5 percent just to equal that same 2.5% in savings?
I will be calling them.
7:47 pm
February 27, 2018
Offline
Norman, i like you.
If a rrif gic's interest is calculated monthly and paid yearly. Will each month incur the full withdrawal amount with none of the hidden offset of the calculated interest? Wow, this is going to be difficult to explain.
A rrif in savings. Interest is paid monthly. So the interest paid offsets the withdrawal amount. Say, you pull $2,000 a month out of a rrif but you make $400 a month in rrif interest. Then the NET reduction to the rrif is $1,600.
With a rrif gic. If you pull that same $2,000 out each month and there is no offset. The interest calculation for the following month would be on (rrif amount - $2,000) not (rrif amount - $1,600) because the interest has not been realized.
8:34 pm
October 27, 2013
Offline
Multi-year GICs are compounded annually IF a compound GIC. If a multi-year GIC is an Annual Pay, the interest is paid out annually and there is no compounding in subsequent years.
8:59 pm
April 6, 2013
Offline
Kidd said
Norman, i like you.If a rrif gic's interest is calculated monthly and paid yearly. Will each month incur the full withdrawal amount with none of the hidden offset of the calculated interest? Wow, this is going to be difficult to explain.
…
With a rrif gic. If you pull that same $2,000 out each month and there is no offset. The interest calculation for the following month would be on (rrif amount - $2,000) not (rrif amount - $1,600) because the interest has not been realized.
That's correct.
The balance for next month's interest calculations will be reduced by the full $2,000 of the withdrawal. The accrued or still-unpaid interest is not part of that $2,000 withdrawal.
12:28 am
February 24, 2015
Offline
In case the calculations are not yet clear, I'll throw in another perspective. The calculations have nothing to do with a RRIF. You started with a GIC that compounds annually at a fixed rate 3% for a fixed term (5 years?). Then you have added a fixed withdrawal amount $2000 at a certain frequency (monthly) with no penalty for the withdrawals.
That results in interest being calculated on the daily balance, but compounded annually (as opposed to compounded monthly in a typical savings account). In your spreadsheet, you need to include the day of the month in which your withdrawal is made. So you have working for you, earning 3%:
$200,000 on March 22
$198,000 after your first withdrawal
$196,000 after your second withdrawal
etc.
$176,000 after your 12th withdrawal
$176,000 plus interest after the compounding date March 22
The interest to be added on the compounding date will be different depending on the day of the month of the withdrawal, which does not need to be the 22nd or the last day of the month. The key is that the interest is calculated daily.
9:29 am
April 6, 2013
Offline
The amortization for daily interest accrual (based on daily closing balances), interest compounding annually, and the given month-end withdrawals would be something like this:
| Date | Withdrawal | Interest | Balance | Days |
Interest Accrued |
Total Accrued |
| 22-Mar-2019 | $0.00 | $200,000.00 | 9 | $147.945205 | $147.945205 | |
| 31-Mar-2019 | $0.00 | $200,000.00 | 30 | $493.150685 | $641.095890 | |
| 30-Apr-2019 | -$2,000.00 | $198,000.00 | 31 | $504.493151 | $1,145.589041 | |
| 31-May-2019 | -$2,000.00 | $196,000.00 | 30 | $483.287671 | $1,628.876712 | |
| 30-Jun-2019 | -$2,000.00 | $194,000.00 | 31 | $494.301370 | $2,123.178082 | |
| 31-Jul-2019 | -$2,000.00 | $192,000.00 | 31 | $489.205479 | $2,612.383561 | |
| 31-Aug-2019 | -$2,000.00 | $190,000.00 | 30 | $468.493151 | $3,080.876712 | |
| 30-Sep-2019 | -$2,000.00 | $188,000.00 | 31 | $479.013699 | $3,559.890411 | |
| 31-Oct-2019 | -$2,000.00 | $186,000.00 | 30 | $458.630137 | $4,018.520548 | |
| 30-Nov-2019 | -$2,000.00 | $184,000.00 | 31 | $468.821918 | $4,487.342466 | |
| 31-Dec-2019 | -$2,000.00 | $182,000.00 | 31 | $463.726027 | $4,951.068493 | |
| 31-Jan-2020 | -$2,000.00 | $180,000.00 | 29 | $429.041096 | $5,380.109589 | |
| 29-Feb-2020 | -$2,000.00 | $178,000.00 | 22 | $321.863014 | $5,701.972603 | |
| 22-Mar-2020 | $0.00 | $5,701.97 | $183,701.97 | 9 | $135.889128 | $135.889128 |
| 31-Mar-2020 | -$2,000.00 | $181,701.97 | 30 | $448.032255 | $583.921383 | |
| 30-Apr-2020 | -$2,000.00 | $179,701.97 | 31 | $457.870773 | $1,041.792156 | |
| 31-May-2020 | -$2,000.00 | $177,701.97 | 30 | $438.169241 | $1,479.961397 | |
| 30-Jun-2020 | -$2,000.00 | $175,701.97 | 31 | $447.678992 | $1,927.640389 | |
| 31-Jul-2020 | -$2,000.00 | $173,701.97 | 31 | $442.583102 | $2,370.223491 | |
| 31-Aug-2020 | -$2,000.00 | $171,701.97 | 30 | $423.374721 | $2,793.598212 | |
| 30-Sep-2020 | -$2,000.00 | $169,701.97 | 31 | $432.391321 | $3,225.989533 | |
| 31-Oct-2020 | -$2,000.00 | $167,701.97 | 30 | $413.511707 | $3,639.501240 | |
| 30-Nov-2020 | -$2,000.00 | $165,701.97 | 31 | $422.199540 | $4,061.700780 | |
| 31-Dec-2020 | -$2,000.00 | $163,701.97 | 31 | $417.103650 | $4,478.804430 | |
| 31-Jan-2021 | -$2,000.00 | $161,701.97 | 28 | $372.136041 | $4,850.940471 | |
| 28-Feb-2021 | -$2,000.00 | $159,701.97 | 22 | $288.776165 | $5,139.716636 | |
| 22-Mar-2021 | -$2,000.00 | $5,139.72 | $162,841.69 |
10:29 am
February 27, 2018
Offline
Norman, thank you very much.
This morning I sat down (with a better understanding) and I came up with numbers very similar to yours. My results are not the exact same but they were close. Thankfully, in a stationary or declining interest rate environment, I will be ahead (barely) at the end of the 2 year GIC term.
Thinking back, I had a 1 year gic rrif before and at that time, I posted here on this forum about how confused I was on the withdrawals and how interest was paid.
https://www.highinterestsavings.ca/forum/rrsps-and-rrifs/converting-rsps-to-rifs/
Thanks again.
** If quitters never win, and winners never quit, what fool came up with, "Quit while you're ahead?"
9:10 pm
April 6, 2013
Offline
Kidd said
Norman, thank you very much.
This morning I sat down (with a better understanding) and I came up with numbers very similar to yours. My results are not the exact same but they were close. Thankfully, in a stationary or declining interest rate environment, I will be ahead (barely) at the end of the 2 year GIC term.
…
You're welcome!
Make sure to round the March 22, 2020 interest payment. A common mistake is to forget to round the accrued interest to the nearest cent when it is paid or compounded. That extra 0.2603¢ in the account balance will cause the subsequent accrued interest calculations to be off by a bit.
Please write your comments in the forum.