Using Excel’s PMT Function to Determine Financing Payments
Using excel to build a financing table can be useful when it comes time to buy a car, purchase a house, or understanding credit cards. The process is not to difficult once a few things are explained:
- First open a new workbook in excel.
- Click on the FX button in the formula bar and search for PMT function and select the PMT option and push OK.
- Next a grid will come up to configure the formula.
- Rate: enter the interest rate divided by the payments per year you make. For example 10% interest on monthly payments would be .1/12
- Nper: This is the total number of payments for the loan.
- PV: This is the current or present value of the loan.
- FV: This is the target balance (in most cases zero, but that can be changed for complex strategies)
- Type: Use 1 if your payments are at the beginning of each period, or use 0 if your payments are at the end of the period.
That is it, once those values are entered push OK and your payment will be calculated. If you understand Excel, you can do a lot with this formula from building payment models to see how extra payments impact your interest and total cost to building full payment schedules. The more you understand about your loans the better you can manage them!