How to calculate comparison interest rate in Excel

Searching for how comparison interest rates are calculated, I found an article on Tomorrow Finance, but unfortunately a lot of the numbers provided no insight into how they were calculated. I hope to break it down further and explain how to calculate comparison interest rates with Excel. The Excel formula can be quite tricky and the parameters vague, so I will go through each of the formula as well.

  • rate – the interest rate of the loan annually equal to 4.63% (the current rate most banks are offering as of today)
  • principle – the loan amount always equal to $150,000 when calculating a comparison rate.
  • period – the period in years always equal to 25 years when calculating a comparison rate.

Payments for a loan

Firstly, calculate the monthly payments for a loan based on constant principle payments and a constant interest rate. The key here is to convert the interest rate to a monthly interest rate and the number of periods also in months.

payment = PMT(rate/12, period*12, principle)

In this case payments equals -$844.86 per month and includes principle and interest. The figure is negative since it is an outgoing.

Total interest payment

total payment = payment * 12 * period

Since the monthly payments are all the same, the total payments is simply the monthly payment multiplied by 12 months by 25 years, this equals -$253,456.66.

total interest = total payment + principle

The total interest is imply the total payment (which is negative) plus the principle, this results in a total interest only of -$103,456.66.

A quicker way to calculate the cumulative interest over the lifetime of a loan is;

total interest = CUMIPMT(rate/12, period*12, principle, 1, period*12, 0)

Additional fees

Next the additional fees over the lifetime of the loan are added, usually these are annual service fees, with nab there is a -$395 annual service fees. The total fee over the life of the loan would be 25 years multiplied by -$395 which equals -$9,875.

total outgoings = total interest + total fees

The total outgoings over the life of the loan would be the total interest and total fees, this would equal -$113,331.66.

outgoings = total outgoings / 25 /12

To get the outgoings per month divide by 300 to get -$877.77 per month.

Comparison rate

Finally, the comparison rate can be calculated;

comparison rate = RATE(period * 12, outgoings, principle)*12

The rate is per month which is multiplied by 12 to get an annual comparison rate, in this case equal to 0.050101482 or 5.01%.

Download the WorkBook how-to-calculate-comparison-interest-rate-in-excel.xlsm.

Free copy of The Building Code of Australia (BCA)

What a free copy of the building code of Australia (BCA), the Australian Government has made it public.

In order to promote public education and public safety, equal justice for all, a better informed citizenry, the rule of law, world trade and world peace, this legal document is hereby made available on a noncommercial basis, as it is the right of all humans to know and speak the laws that govern them.

National Construction Code Series 2012 VOLUME ONE HTML
National Construction Code Series 2012 VOLUME TWO HTML
National Construction Code Series 2012 VOLUME ONE PDF
National Construction Code Series 2012 VOLUME TWO PDF

The benefits of builder brokers

It is known that mortgage brokers and insurance brokers are useful to get the best possible service at the best possible price. I thought I would share my knowledge of builder brokers, and whether they can benefit those building a home or development.  Note this analysis and opinion is purely based on my knowledge and experience in Perth, Australia, and I have never actually engaged the services of a builder broker.

Similar to a mortgage and insurance broker, a builder broker claims that they can build a dwelling at the best price and terms. The argument is that by quoting on the same plans to various builders, since all the builders will be quoting on the same design, the most favourable price and terms can be selected. This is different to approaching builders individually, who will create their own designs and provide a cost, since all designs are different, it is difficult to compare like for like. Though, there is an advantage that each builder may produce a design that they can optimally construct due to their past knowledge.

Design

A builder broker will create the dwelling design, usually the development approval drawings, so that it can be submitted to council for approval. The builder broker may guarantee council approval, which is a definitely a good thing. The price of these drawings may be significantly less than getting an architect to do similar, with the builder broker reasoning that their return are from the builder on signing up, so it is in their interest to take it that far.

There are a few catches, the copyright of the drawings will belong to the broker builder and will be licensed only if you use their services.  Let’s say you have received development approval, paid your application fee, a few hundred or thousand dollars depending on the size, you are now at the mercy of the builder broker, if you don’t like their choice of builders, the subsequent quotes, etc, you can’t take your plans somewhere else. This leads on to the next point.

Conflict of Interest

There is a difference between a mortgage or insurance broker and a builder broker, the former can easily be compared by yourself, when you receive a loan from a mortgage broker it can be compared with any number of banks, all on similar terms and conditions. Similarly, with an insurance broker, the insurance premiums can all be easily compared. A builder broker, you don’t have a licence to use the copyright except if you chose to progress the build through the broker, with the builder broker arguing that this is how they make their fees. Understandably, this is true, but even throwing your own preference for builder can be met with backlash.

Firstly, though, let’s talk about the builder broker’s fee, besides the initial ‘foot in the door’ payment for producing the drawings, the bulk of the builder brokers fee is the commission from the builders. That is, a percentage of the building cost is given to the builder broker, let me say that again, the more you pay to the builder, the more the builder broker receives, how is that is your best interests. Which leads to my next point.

Transparency

The builder broker will cloud however they can the comparison between builders, in order to favour the quote that gives them the largest cut. Firstly, each builder may give different referral fees, depending on past relationship, etc, you will never know, and that is why they are hesitant to introduce builders that are not on their shortlist, they may need to build new relationships, or know that there is not much commission through them. Secondly, they summarise the quotes into a simple table, so you can easily compare, you may never see the quotes, and their summaries are arranged to lead you choosing a builder that the builder broker prefers. Thirdly, brokers argue that they are simply taking the commission that the sales person of the builders would normally take, this may be true if the broker has a good relationship with a builder, but again, there is no transparency, and unlike a sales person what incentive does the broker have to negotiate a lower price.

Alternatives

A better alternative to a builder broker is getting a draft person or architect to design the dwelling for you. Once council approved, then approach builders individually, yes, you will be dealing with the builder’s salesperson, but if you don’t like what one builder is offering, simply move to the next.

In fairness, don’t show one builder’s quote to other builders, instead give them a rough indication of how they are placed and allow them to adjust.

AFS Walling Solutions – permanent formwork concrete walls review

Recently I did some research into the AFS Logic Wall product as an alternative to brick for a development I am working on.

AFS is a permanent formwork concrete wall solution, what this means is instead of creating the timber frame on site for the concrete pour, a factory creates a shell which is then assembled on site and filled with concrete.

The benefits are quicker wall construction times, days not weeks, as there is minimum site works. This initially seams like a huge benefit, but only after speaking to a number of industry people are the true costs discovered.

As a start, read through this AFS Assessment by the Ceramic Advisory Services, though the information may be outdated and biased.

I received the following information from an AFS distributor, April 2014;

Typically AFS150 would be supplied and installed and filled with concrete for approx $ 215/m2, ready for applied finishes.

Compared to a brick, the costs is fairly similar, especially if comparing to double brick. But there are some additional costs that are not accounted for.

From the distance, there looks like there is a huge advantage to reduced wall thicknesses, I was informed 150mm AFS could be used in place of my 230mm double brick. There are a couple of catches though, only electrical conduit can be set in the concrete, water pipes can’t and so a separate stud wall is needed, adding about 75mm and additional costs.

AFS wall have a specified insulation R value, though the wall is concrete only, if you can’t meet the required insulation then additional will be required. With double brick construction, the insulation can be installed in the cavity, there is no cavity with the AFS so again a separate stud wall is needed.

When you need a thin wall, brick is a minimum 90mm thick, whilst AFS is 110mm, possibly due to the cement formwork being 10mm in thickness on each side and unable to support any load.

The slab needs starter rebar for the formwork, whilst brick does not, again, adding additional costs.

Finally, there are only a handful of builders that work with AFS, in Perth I was given the names of four builders, severely restricting the choice.

Calculate last business day in month with Excel

Here is a quick post to calculate the last business day in a month taking into account holidays.

This is useful for with finance calculations and wanting to know a bank’s last banking day in a month.

=WORKDAY(EOMONTH(A2,0)+1,-1,holidays!A:A)

Where;

  • Cell A2 is the reference month cell
  • Range holidays!A:A is a list of holidays

Home loans – Fixed vs. Variable interest rates

I saw an interesting chart on Canstar which plotted the history of RBA Cash Rate, variable and 3-year fixed interest rates. This data is all available on the Reserve Bank of Australia website, look for A2 and F2 statistics.

History of cash rate, variable and fixed

I thought I would take it a step further and determine from past history if the 3-year fixed or variable rate would have been a better choice at a given time. I always believe that choosing a 3-year fixed interest rate was like gambling, it could either work in your favour or not, I am curious to see if I am right.

Given monthly data for the variable interest rate, to calculate which would have been the better choice I average thirty-six months of future variable rates. Because it is constantly looking three-years into the future, it can be observed that the data finishes in February 2010.

3-year average future variable vs. 3-year fixed

What can be observed is that the fixed rate is sometimes a better option (when under the three-years future average) and sometimes it is worse. Pre 2000, the fixed was mostly a poor choice, post 2000 the fixed rate is lower most of the time.

Finally, a difference of the two and it can be seen that it really jumps back and forth between good and bad. If we take the average of the difference we get 0.41%, which means the 3-year fixed rate is generally an interest rate of 0.41% worse.

3-year average future variable vs. 3-year fixed difference

Out of 233 months, 121 months (or 52% of the time) the fixed had a worse off interest rate and 112 months (or 48% of the time) the fixed had a better interest rate.

However, if we look at the 121 months from 2000 onwards, the variable interest rate is better off by an average interest rate of 0.23% which occurs about two-thirds of the time.

In conclusion, 3-year fixed rates these days do seem to have a good chance of having a lower interest rate for the locked-in period. That combined with the advantage of having a non fluctuating interest rate makes it a good choice.
I still would avoid them though, fixed rates make it difficult to switch lenders due to the extra fees payable.

To trust or not to trust – the benefits of a discretionary (family) trust – final comparison

I have discussed the benefits of a discretionary trust aka family trust for minimising capital gains tax (CGT). I have also discussed the benefits of a discretionary trust from minimising taxable income. For this last part I am going to compare a discretionary trust against a direct 50-50 split over an average person’s lifespan.

Again similar to the previous examples a 50-50 split is exactly that, two persons owning half a property each, any income is split equally. Also as per previous examples, one person will be earning a secondary external income of $100,000. As before, I have assumed a $1,000 annual fee for trust management.

The following examples try to emulate a real world situation, the purchase of a property, initially purchased under a mortgage with some rent, initially the property will be negative geared. I have assumed all income after tax goes into paying the mortgage, this would never be the case in the real world, so the examples below show the property being paid off much quicker than normal. Finally, once the properties has been paid off savings are accumulated which includes interest. This interest in the same as the mortgage interest, which wouldn’t be the case as it would always be slightly lower.

As previously discussed a 50-50 split is always more beneficial compared to a discretionary trust when the properties are negatively geared, so to make things interesting I have added cases were they begin with a 50-50 split, then once the property is positively geared it changes to a discretionary trust.

The following scenarios are used for the situations;

  • 50-50 – a split of the property when each person owns an even 50%
  • Trust 2pers – a discretionary trust with 2 beneficiaries
  • Combo – starting with a 50-50 split and then moving to a trust with 2 beneficiaries once the property is positively geared
  • Trust 3pers – a discretionary trust with 3 beneficiaries
  • Combo2 – starting with a 50-50 split and then moving to a trust with 3 beneficiaries once the property is positively geared

Example 1 – mortgage $500,000, rental yield 2.5% and interest 7%

First a 30 year chart of the yearly income after tax

$500000, 30 year chart of the yearly income after tax

The first abnormality that can be observed on the combo situations is a bump in income at around the 5 year mark, this is the point where the property goes from negatively geared to positively geared, and as I stated above, this is when the combo situations move from a 50-50 split to a trust. But why the bump/ well, because you have changed the title from two persons to a trust, stamp duty is payable, for a $500,000 property roughly $18,000 is payable. Stamp duty is what makes it unappealing to move to and from a trust situation, it is the believed reason key decisions about the trusts need to be made early on, as it is expensive to change your decision later on.

Looking at the cumulative wealth over 30 years, this provides a clearer picture

$500000, cumulative wealth over 30 years

Straight away, at 30 years the order of scenarios providing the best return in descending order are the second combo, the three person trust, the 50-50 split, the first combo and lastly the two person trust. The difference between the best and worst results is $280,000 which is very significant for a 30 year projection. The difference at around 10 years is only $20,000, since the trusts are playing catch-up from the period of negatively gearing. If you look closely it can be observed the two combo scenarios have a very slight dip around that fifth year corresponding to the stamp duty.

Example 2 – mortgage $1,000,000, rental yield 2.5% and interest 7%

Again, a 30 year chart of the yearly income after tax

$1000000, 30 year chart of the yearly income after tax

A couple of things to notice, first the bump at the 13 year mark signals where the property moved from negative geared to positive geared, a larger mortgage means longer time to pay off. A higher valued property also means more stamp duty for $1,000,000 the stamp duty is around $43,000.

Looking at the cumulative wealth over 30 years

$1000000, cumulative wealth over 30 years

Firstly, the order of scenarios after a 30 year projection has changed, the 50-50 split and combos are in the lead, whilst the two trust scenarios are trailing.  The difference between the best and worst results is $250,000 which is very significant for a 30 year projection.  We can see the affects of the stamp duty a lot more clearly on the light blue and green lines and the trusts only scenarios never recover from the start.

But what if the cumulative wealth is projected over 50 years

$1000000, cumulative wealth over 50 years

The trust scenarios are back in the lead. Because of the larger time negatively geared and larger stamp duty, the trust scenarios needed more time to ‘catch up’.

Example 3 – mortgage $250,000, rental yield 2.5% and interest 7%

As above, a 30 year chart of the yearly income after tax

$250000, 30 year chart of the yearly income after tax

As expected, there is a very short period of negative gearing, roughly 3 years. Due to the relatively low value property, the stamp duty is also minor at $7,000.

Looking at the cumulative wealth over 30 years

$250000, cumulative wealth over 30 years

As expected, due to the short negatively geared period, the trusts are beneficial for the majority of the projection period.

Conclusion

So what does this all mean?

Making the correct decision about trusts can save you hundreds of thousands of dollars in the long run. In general if a property is positively geared it would benefit if it is in a trust and if a property is negatively geared it would benefit if owned individually.

The good news is, if planning to hold for the long term it, it can be decided to move to a discretionary trust scenario, as the benefits will eventually out way the costs. When the property is positively geared move it to a trusts, but be prepared to hold it to offset the stamp duty.

To trust or not to trust – the benefits of a discretionary (family) trust for minimising income tax

A few weeks ago I blogged about the insignificant benefits of a discretionary (or family) trust compared with a basic 50-50 joint split when it came to Capital Gains Tax (CGT), this week I will compare the two when it comes to rental income.

Rental income is different to CGT in that rental income is generally a lot less, and secondly, it is an annual income so any tax benefits can be compounded yearly. The results should be the same for values when Capital Gain and Rental Income are he same.

Again, I have based the below charts on a 50-50 split were one person has an supplementary income of $100,000 per year. I have ranged the annual rental income from minus $250,000 to plus $250,000, this is a huge sum for rental income and would require around $5000 rental income per week, and this is all after management costs and deductions.

A basic comparison between the two, with annual rental income versus total tax payable, it can be observed that that at values less than -$163,500 (a significant yearly loss), the 50-50 split can use negative gearing to offset the individual $100,000 supplementary income, whilst the discretionary trust cannot take advantage of negative gearing. This means there will always be a $25,000 tax bill as long as the properties are negatively geared.  As the net rental income approaches zero (or neutrally geared), the 50-50 split tax and trust tax merge.

50-50 split tax versus family trust tax

Finally, once the property is positively geared, the benefits of a discretionary trust are visible; the first $18,200 is tax free and following from there the tax bill is always slightly lower than the 50-50 split.

Plotting the tax difference between the two, a positive value being when a discretionary trust is beneficial, it can be observed that the maximum difference is around $3,300 with incomes around $18,000 to $36,000, this is a significant yearly saving.

The tax difference between a 50-50 split and family trust

Looking at the percentage difference compared to the yearly income, it can be again observed that at $18,000 or income a maximum of around 18% can be saved when a discretionary trust is used. It dips and stops at around a yearly income of$70,000 were it then hovers around 1% percentage difference.

The tax percent difference between a 50-50 split and family trust

So what does this all mean?

Firstly, a discretionary trust should not be used for negatively geared properties, this is obvious.

Secondly, a discretionary trust has significant benefits when used for positively geared properties. And because this in annual income, these benefits are compounded yearly. A saving of $3,300 yearly is a saving of $33,000 over ten years.

So one would think it would be wise to move the properties into a discretionary trust as soon as the properties are positively geared.

I will investigate this in my next post.

Update September 2012

Something I failed to mention was that whilst a trust is not beneficial for negative gearing, losses stay in the trust, meaning when the income is positively geared, tax will be minimised while the losses are accounted for.