PMT Function in Excel - how to use it to calculate loan payment
Posted on: 08/10/2021
One of Excel's most popular financial functions is the PMT function. The PMT function will calculate your payments for a loan based on the interest rate, loan amount, and the number of periods. You would use the PMT function to calculate a car or house loan.
PMT function for a car loan
YouTube video
PMT Function Syntax
PMT(rate, nper, pv, [fv], [type])
Examples of the PMT Function
Example 1: =PMT(4.25%/12,30*12,350000)
Example 1 is a PMT function not referencing cells. You will get a negative answer since it is money you have to pay. To make the answer positive, use -350000 in the formula.
Example 2: =PMT(b4/12,b3*12,-b2)
Example 2 is referencing cells. See the screenshot below.
Five arguments in the PMT function
-
rate - The interest rate for the loan.
-
nper - The total number of payments for the loan.
-
pv - The present value, or the total value of all loan payments now.
-
fv - [optional] The future value or a cash balance you want after the last payment is made. Defaults to 0 (zero).
-
type - [optional] When payments are due. 0 = end of the period. 1 = beginning of the period. Default is 0.
Tip: Wondering why [fv] and [type] are in square brackets? The brackets mean they're optional. If you don't include values for fv and type in your formula, Excel assumes your balance will be $0 at the end of the loan, and that your payments are due at the end of the period.
PMT function for a house loan
Other Excel articles
Excel - Find & Highlight Duplicate Rows - 3 Methods | Conditional Formatting
Microsoft Excel can find duplicates easily with Conditional Formatting. The issue is Conditional Formatting finds duplicates based on the cell value, but I want to find duplicate rows. I use three methods in the video to find duplicate rows. I start off with CONCATENATE, then use the TEXTJOIN function, and finally COUNTIFS with no helper column. To highlight the rows I use Conditional Formatting using a formula.
How to use the TODAY function in Excel
The TODAY function returns the current date and is updated automatically when you open an Excel file. The TODAY function has no arguments. In this video, I'll use the TODAY function in an accounting exercise using Accounts Receivable. You would use the same for Accounts Payable.
Excel Convert Text to Numbers - Four easy methods
Need to convert text to numbers in Excel? I'll demonstrate four methods in the easy-to-follow article and video. If you need to analyze data in Excel, numbers frequently come in as text and need to be converted.
Excel - 5 easy ways to identify numbers stored as text
In Excel, numbers that are stored as text can cause unexpected results. It is important before you analyze numbers to make sure there are not stored as text. In this video, I'll demonstrate five methods to quickly find numbers stored as text. Numbers as text usually happens when you pull data from another data source into Excel.
Margin of Safety vs. Break-even point in Excel
Break-even point (BEP) is the level of sales where the total cost - fixed cost and variable cost - equals total revenues. Break-even is where Net Income is zero. There is no profit and no loss.
The Margin of Safety (MoS) is a difference between actual/budgeted sales and the level of breakeven sales.
Chris Menard
Chris Menard is a Senior Training Specialist at SurePoint Technologies. Chris is certified in Excel, Word, PowerPoint, and Outlook. Menard has a YouTube channel with other 600 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 7 million viewers have very appreciated. Because of Chris's certification and expertise with Microsoft, Chris is a proud member of Microsoft's Creator Team. Being a member of Microsoft's Creator Teams means many of his videos are available on Microsoft 365 YouTube channel and Microsoft support websites.
Categories