Now that you know how to find any missing value, it's time to create the amortization table.
RATE should always be divided by 12 when your NPER is based on months otherwise, you'll get a severely deflated value. Total Loan Amountįinally, if you want to know how much you can borrow given a specific interest rate, the amount you can afford monthly, and the number of months you're willing to pay the loan, use the PV function: =PV(RATE,NPER,-PMT, Our Simple Excel loan calculator spreadsheet offers the following features: works offline easily savable allows extra payments to be added. When you're unsure how many months you're going to pay a loan, but you know how much you're borrowing and how much you can afford to repay every month, you can use the NPER function: =NPER(RATE,-PMT,PV,)Īgain, divide the RATE by 12 since you're computing the number of months you will repay a loan. We have offered a downloadable Windows application for calculating mortgages for many years, but we have recently had a number of people request an Excel spreadsheet which shows loan amortization tables. This formula will give you the monthly rate, so don't forget to multiply the final result by 12.
If you need to determine the interest rate, use the RATE function: =RATE(NPER,-PMT,PV,) Just remember to divide the RATE by 12, as we're computing for the monthly payments. To compute the monthly payments, use the PMT function: =-PMT(RATE,NPER,PV,)
