{"id":33644,"date":"2023-12-28T04:29:54","date_gmt":"2023-12-28T03:29:54","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=33644"},"modified":"2024-05-14T20:49:09","modified_gmt":"2024-05-14T18:49:09","slug":"pmt-function-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/pmt-function-in-excel\/","title":{"rendered":"Mastering PMT Function in Excel &#8211; 3 Examples"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-33771 size-large\" title=\"PMT Function in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-1024x576.png\" alt=\"PMT Function in Excel\" width=\"1024\" height=\"576\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-1024x576.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-768x432.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel.png 1280w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/free-excel-online-training-courses\/\" target=\"_blank\" rel=\"noopener\"><strong>Microsoft Excel<\/strong><\/a> is a powerful tool that offers <strong>many functions to help users perform complex calculations and analyses<\/strong>. One such function widely<strong> used in financial modeling and planning<\/strong> is the <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-as-time-value-of-money-calculator\/\" target=\"_blank\" rel=\"noopener\"><strong>PMT<\/strong><\/a> function. The PMT <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-mirr-function\/\" target=\"_blank\" rel=\"noopener\">function\u00a0calculates<\/a><strong> the periodic payment<\/strong>\u00a0against an investment or a loan at a\u00a0<strong>constant interest rate<\/strong>\u00a0for a specified<strong> time<\/strong>. The <strong>syntax<\/strong> of the PMT function is :<\/p>\n<p><strong>=PMT (rate,\u00a0nper,\u00a0pv,\u00a0[fv],[type])<\/strong><\/p>\n<p>where,<\/p>\n<ul>\n<li><b class=\"ocpRunInHead\">Rate &#8211; <\/b>Required. The interest rate for the loan.<\/li>\n<li><b class=\"ocpRunInHead\">Nper &#8211; <\/b>Required. The total number of payments for the loan.<\/li>\n<li><b class=\"ocpRunInHead\">Pv &#8211; <\/b>Required. The present value, or the total amount that a series of future payments is worth now; also known as the principal.<\/li>\n<li><b class=\"ocpRunInHead\">Fv &#8211; <\/b>Optional. The<a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/compound-interest-calculator-in-excel\/#Method_2_%E2%80%93_FV_Formula\" target=\"_blank\" rel=\"noopener\"><strong> future value<\/strong><\/a>, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.<\/li>\n<li><b class=\"ocpRunInHead\">Type &#8211; <\/b>Optional. The number 0 (zero) or 1 and indicates when payments are due.<\/li>\n<\/ul>\n<p>In this article, we will cover the following three examples in detail &#8211;<\/p>\n<div class=\"contents\">\n<h2>Table of Contents<\/h2>\n<ul>\n<li><a href=\"#1\"><strong>Simple Loan Calculation<\/strong><\/a><\/li>\n<li><a href=\"#2\"><strong>Loan with Balloon Payments<\/strong><\/a><\/li>\n<li><a href=\"#3\"><strong>Payment at the Beginning<\/strong><\/a><\/li>\n<\/ul>\n<\/div>\n<p>Let us explore the methods one by one!<\/p>\n<h4>Download the Excel Workbook below to follow along and understand How to Use PMT Function in Excel \u2013 <a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel.xlsx\" target=\"_blank\" rel=\"noopener\"><span class=\"ctaText px-3 text-capitalize\"><strong>download excel\u00a0<\/strong><strong>workbook<\/strong><\/span><span class=\"postTitle px-3\">PMT-Function-in-Excel.xlsx<\/span><\/a><\/h4>\n<p>&nbsp;<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/pmt-function-in-excel\/#Simple_Loan_Calculation\" title=\"Simple Loan Calculation\">Simple Loan Calculation<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/pmt-function-in-excel\/#Loan_with_Balloon_Payment\" title=\"Loan with Balloon Payment\">Loan with Balloon Payment<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/pmt-function-in-excel\/#Payment_at_the_Beginning\" title=\"Payment at the Beginning\">Payment at the Beginning<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Simple_Loan_Calculation\"><\/span><strong><span id=\"1\" style=\"color: #ff0000\">Simple Loan Calculation<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Suppose you took a <strong>loan of $10,000 with an annual interest rate of 5% for 3 years<\/strong>. You can use the <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/category\/formulas\/other\/financial\/\" target=\"_blank\" rel=\"noopener\"><strong>financial formula<\/strong><\/a> in Excel to <strong>calculate the monthly payment<\/strong> that you need to make. The PMT formula that you can use for this example &#8211;<\/p>\n<p><strong>=PMT(5%\/12,3*12,10000)<\/strong><\/p>\n<p>where,<\/p>\n<ul>\n<li><strong>5%\/12<\/strong> represents the monthly interest rate.<\/li>\n<li><strong>3*12<\/strong> denotes the total number of payment periods (3 years * 12 months).<\/li>\n<li><strong>10000<\/strong> signifies the present value of the loan.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-33645 size-full\" title=\"PMT Function in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-1.png\" alt=\"PMT Function in Excel\" width=\"731\" height=\"312\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-1.png 731w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-1-300x128.png 300w\" sizes=\"(max-width: 731px) 100vw, 731px\" \/><\/a><\/p>\n<p>Once you run this formula in Excel, it will provide the <strong>monthly payment i.e. $300<\/strong> of the loan.<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Loan_with_Balloon_Payment\"><\/span><strong><span id=\"2\" style=\"color: #ff0000\">Loan with Balloon Payment<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Balloon Payment refers to a type of loan where the borrower makes smaller regular payments throughout the loan period, and a significant, larger payment\u2014the balloon payment\u2014is due at the end. Let us consider a scenario where you take a loan of $20,000 with a 6% interest rate over 4 years but with a balloon payment of $5,000 at the end.<\/p>\n<p>The PMT function can be used to calculate the monthly payments that you need to make &#8211;<\/p>\n<p><strong>=PMT(6%\/12,4*12,20000,-5000)<\/strong><\/p>\n<p>where,<\/p>\n<ul>\n<li><strong>6%\/12<\/strong> represents the monthly interest rate.<\/li>\n<li><strong>4*12<\/strong> denotes the total number of payment periods.<\/li>\n<li><strong>20000<\/strong> signifies the present value of the loan.<\/li>\n<li><strong>5000<\/strong> is the balloon payment at the end.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-2.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-33646 size-full\" title=\"PMT Function in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-2.png\" alt=\"PMT Function in Excel\" width=\"719\" height=\"326\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-2.png 719w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-2-300x136.png 300w\" sizes=\"(max-width: 719px) 100vw, 719px\" \/><\/a><\/p>\n<p>Once you run this <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/recovery-rate\/\" target=\"_blank\" rel=\"noopener\">formula in Excel<\/a>, it will provide the<strong> monthly payment i.e. $377<\/strong> of the loan.<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Payment_at_the_Beginning\"><\/span><strong><span id=\"3\" style=\"color: #ff0000\">Payment at the Beginning<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The PMT function assumes that<strong> payments are made at the end of each period by default<\/strong>. However,<strong> if payments are made at the beginning<\/strong> of each period, you can specify this by<strong> using the optional type argument<\/strong> in the PMT function.<\/p>\n<ul>\n<li>If the <strong>type is 0 or omitted<\/strong>, payments are assumed to be <strong>due at the end<\/strong> of the period.<\/li>\n<li>If the <strong>type is 1<\/strong>, payments are made at the <strong>beginning<\/strong>.<\/li>\n<\/ul>\n<p>Suppose you have a $20,000 loan with an annual interest rate of 5% paid over 3 years, and the payments are due at the beginning of each period. You can use <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/nper-function\/\" target=\"_blank\" rel=\"noopener\">the PMT function<\/a> as follows &#8211;<\/p>\n<p><strong>=PMT(5%\/12,3*12,10000,,1)<\/strong><\/p>\n<p>where,<\/p>\n<ul>\n<li><strong>5%\/12<\/strong> represents the monthly interest rate.<\/li>\n<li><strong>3*12<\/strong> denotes the total number of payment periods (3 years * 12 months).<\/li>\n<li><strong>10000<\/strong> signifies the present value of the loan.<\/li>\n<li>FV argument is omitted.<\/li>\n<li><strong>1<\/strong> is the type argument because payments need to be paid at the beginning of the period.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-3.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-33647 size-full\" title=\"PMT Function in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-3.png\" alt=\"PMT Function in Excel\" width=\"643\" height=\"328\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-3.png 643w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/12\/PMT-Function-in-Excel-3-300x153.png 300w\" sizes=\"(max-width: 643px) 100vw, 643px\" \/><\/a><\/p>\n<p>Executing this formula in Excel would give you the <strong>monthly payment i.e. $298<\/strong> for the loan, taking into account<strong> <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/calculate-monthly-investment-excels-fv-formula\/\" target=\"_blank\" rel=\"noopener\">payments<\/a> made at the beginning<\/strong> of each period.<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/free-microsoft-excel-online-course\/\" target=\"_blank\" rel=\"noopener\"><strong>Microsoft Excel<\/strong><\/a>&#8216;s <a href=\"https:\/\/support.microsoft.com\/en-us\/office\/pmt-function-0214da64-9a63-4996-bc20-214433fa6441\" target=\"_blank\" rel=\"nofollow noopener\"><strong>PMT function<\/strong><\/a> is a valuable tool for financial modeling and planning, allowing users to calculate <strong>periodic payments<\/strong> for loans or investments with ease. In this article, we explored three essential methods: Simple Loan Calculation, Loan with Balloon Payments, and Payment at the Beginning which empowers users to <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/window-dressing\/\" target=\"_blank\" rel=\"noopener\">master this powerful financial<\/a> tool.<\/p>\n<p><span style=\"font-weight: 400\">Learn more about Formulas with our <\/span><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\"><strong>101 Advanced Excel Formulas &amp; Functions Examples<\/strong><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Microsoft Excel is a powerful tool that offers many functions to help users perform complex calculations and analyses. One such function widely used in financial modeling and planning is the PMT function. The PMT function\u00a0calculates the periodic payment\u00a0against an investment or a loan at a\u00a0constant interest rate\u00a0for a specified time. The syntax of the PMT [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":33771,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"Mastering PMT Function in Excel - 3 Examples","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[319],"tags":[1514,1511,1513,1512],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/33644"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=33644"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/33644\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/33771"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=33644"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=33644"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=33644"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}