1. Excel For Finance Tips - Calculate Compound Interest

Nick's picture

The very first tip is also the basis of finance

- how to Calculate Compound Interest in Excel.

When comparing interest rates for loans or investments, it is important to compare them on an equal basis.

Loan 1: 6% per year.

Loan 2. 5.9% per year compounded quarterly

Which loan is cheaper ? It looks like Loan 2. Well, it turns out that loan 1 is in fact cheaper due to the quarterly compounding effect of loan 2. Every quarter, 5.9%/4 (4 times a year) is added onto the loan amount. The compounding effect of this means that the annual equivalent rate is 6.03% which is higher than Loan 1 (annual 6%).


Download my spreadsheet on how to calculate annual equivalent rates for the 2 loans, and find out which one is cheaper.

The conclusion is that the compounding effect doesn't make an enormous difference - in this case 0.14%.. However, if the loan is 100,000, the annual repayment difference is 140 between the same rate quoted annually and quarterly. Better to have that 140 in your pocket than giving it away !


Training Video on how to Calculate Compound Interest in Excel: