Calculating principal repayment for offset savings

Hi Pirate

This might help a little

$450k x 9% / 12 = $3375 which is the interest component each month (ignores the fact that some months are not equal to others)

Try this link to work out the principal (saves me typing the formula long hand)
http://www1.infochoice.com.au/distributions/10379/Amortisation.asp

P & I per month = $3620.80

Difference between the 2 is the principal reduction each month being $245.80

With a P&I with each additional payment the ratio of interest/prinicple changes.  In the first year the majority of the repayment is interest.  What you can do is each year recalculate the difference between in IO and P&I and increase the principle payment into the offset account (this amount would equal the reduction in interest you are currently paying due to the increase balance in the offset).

The method you used to calculate the figure of $1250 would achieve the desired effect of having $450K after 30years.  With the amount of interest you pay per month calculated at $3375, this would only be for the 1st months interest payment.  This figure would reduce each month as the offset account balance builds.  The problem with this is is you initial few years you will be paying alot more out of you pocket each month but in the long run you will save alot more interest payments

the problem you are having is due to the banks using a daily interest calculation and charging interest at the end of the month.
Ring the bank and ask them if interest is calculated by 360 days or 365 days it depends as US system is 360 days as public holidays are not counted.
so get the interest rate say 9% and divide by 365
take the balance each day and multiply by 9% divided by 365 and calculate the daily interest amount.
add each daily balance interest calculation for the whole month and you will get the interest amount charged. It is fairly close to what the bank works out. I have not been able to get an exact match.
I used a formula like
=D4*(H$1/100)/360*14
D4 being the previous balance over the last 14 days as they were the same each day for 14 days
h$1 is the interest rate the $ lets you change h1 for the whole spreadsheet
I adjust the formula for when repayments are made or interest is charged
the balance is the previous balance + possible monthly interest charge – repayment
there is a way of calculating the 14 from excel from two different dates but I can't remember how I did it previously.

Alternatively I have found the loan amortization template in excel office 2003 to be very useful for working out this problem
rather than re inventing the wheel.
File – New – select templates on-line in right hand side of screen and then find loan amortization on web site

My template comes up with $245.80 per month principal and $3375 interest based on monthly repayments
                                                    $56 per week principal and $778.85 interest per week
Weekly payments saves you interest over the long term as the interest is calculated daily !

Having just looked at the template an interesting situation occurs that you may have not thought of.

As you pay down the balance over time the principal payment increases due to the interest being charged each month being reduced while the loan repayment stays the same with a P & I loan.

Hello Pirate

While it's a nice exercise to see what the minimum amount is that needs to be deposited into an offset account to be able to pay out the loan after 30 years, I assume that you realise that this is not the best use of an offset account.

The beauty with an offset account is that while it can save you masses of interest it doesn't actually effect the loan it's coupled to. The idea is to deposit all of your income into the offset account as each dollar that is there for even one day saves you that days interest. You can then use this account to pay for both personal and business expenses. Some people use their credit card during the interest free period so as to be able to leave the cash in the offset account for as long as possible. Obviously the CC needs to be fully paid off each month as it becomes due. This method is for the financially disciplined only but will save a lot of interest.

You probably know all of this already but just in case ………

Cheers
Elka  

Hello Pirate

What you describe is not "wrong". It just doesn't give you the most interest savings.

In fact if you know that you are not financially disiplined (i.e. if you know that the only way you will not overspend on lifestyle each month is if you first allocate that portion) then your method is better for you.

There is nothing wrong with the balance in the offset account fluctuating each month as long as the general trend is upwards.

In case you missed it here is a link to a post by Qlds007 which explains some of the other benefits of an offset account.

https://www.propertyinvesting.com/forums/getting-technical/finance/4325393?#comment-177325

Cheers
Elka