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


Calculate-Compound-Interest-in-Excel


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: