Ever wondered how they calculate the monthly instalments when you take out a auto- or a housing-loan? There is an easy formula in Excel (or other spreadsheet programs for that matter), to do so. Here is a little tutorial:
Write principal amount, period and interest rate as shown above in three different cells. Enter formula in fourth cell: =PMT(RATE, NUMBER OF PAYMENTS, AMOUNT). Enter cell address for rate, number of payments and amount. Look at the formula in above image. Since the payments are monthly, interest rate is 1/12 of the annual interest rate, and the number of payments is 12 times the number of years.
If you want it easier, or want to see it in action, download a sample file here. This file shows all related formulas and also a bonus loan amortization chart. Want any other Excel tips? Ask in your comments.
