In Excel, you can calculate the monthly payments required to pay off a fully amortized loan using the PMT function.
- [Instructor] Most loans, whether between businesses,…or made by businesses to individual borrowers,…are fully amortized.…Fully amortized means that the monthly payments…made over the term of the loan,…payoff the principal, and all accrued interest.…In Excel, you can calculate the monthly payments required…to payoff a fully amortized loan using the PMT function.…I'll demonstrate how to use this function.…My sample file is the Payment Excel Workbook.…You can find that in the Chapter One folder,…of the exercise files archive.…
The PMT function requires three arguments,…and there are two more that are optional.…The first required argument is the interest rate,…which is 4.75% in this case, that's the annual rate.…So, if you're making payments on a monthly basis,…we'll need to divide that by 12,…the number of months in a year.…Next is the number of payments that you'll make,…called the number of periods, in this case it's 360,…which assumes a 30 year loan with payments made…each of the 12 months of the year.…Present value is the amount that you borrow,…
LinkedIn Learning (Lynda.com) is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Calculating the effect of interest rates and inflation
- Finding the arithmetic and geometric means of growth rates
- Calculating the future and present value of an investment
- Calculating loan payments for a fully amortized loan
- Calculating the effect of paying extra principal with each payment
- Finding the number of periods required to meet an investment goal
- Calculating net present value and internal rate of return
- Building a cash tracking worksheet
- Visualizing cash flows using a waterfall chart