Note: If you want to learn more about other google sheet functions, we have a post with 100+ Google Sheet Functions<\/a><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n When it comes to finances, it\u2019s extremely important to get everything right. By using the PMT formula, not only are you avoiding any risk of human error to receive an accurate result, but you can also have a clear understanding of your payments long-term. The PMT works in any long-term payment situation, whether it\u2019s for 30 months or 30 years.<\/p>\n Here are just a few examples of when you\u2019d use the PMT formula:<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The PMT formula is used to calculate constant payments that have a constant interest rate. You can calculate the payments for the loan via the given loan amount, interest rate, and the number of payments.[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Here\u2019s the following PMT formula:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=PMT(rate, number_of_periods, present_value, [future_value, end_or_beginning])[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Please note:<\/b><\/p>\n > If you\u2019re paying a monthly payment for 4 years, then your number_of_periods<\/b> value will be 12*4. Now that we understand what the PMT formula is and what it can be used for, let\u2019s explore how to use the PMT formula in Google Sheets.<\/p>\n [\/et_pb_text][et_pb_text admin_label=”Connections T – Automate between spreadsheets – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button-light vertical-banner-container” _builder_version=”4.27.4″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” saved_tabs=”all” global_colors_info=”{}” global_module=”255443″ theme_builder_area=”post_content”]<\/p>\n Automate data transfers between spreadsheets Find out how<\/a><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let\u2019s say I am an entrepreneur who has just started my new business. I have taken a loan out of $20,000, with an annual interest rate of 9%.<\/p>\n I am paying back in monthly installments over two years – 24 periods.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/05\/PMT-formula.png” alt=”PMT formula 0″ title_text=”PMT formula” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Using the PMT formula, I can calculate exactly how much I will have to pay back each month.<\/p>\n In order to calculate the amount I will have to pay at the end<\/strong> of every month, the PMT formula should look like this:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=PMT(A2\/12,A3,A4)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Here is my result. I will have to pay $913.69 every month to pay back my loan (with interest) over 2 years.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/07\/4-1.png” alt=”PMT formula 1″ title_text=”4″ align=”center” _builder_version=”4.17.4″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now let\u2019s say I want to pay my loan at the beginning of every month. The PMT formula will look like this:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=PMT(A2\/12,A3,A4,,1)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]Please note:<\/b> As we haven\u2019t inputted the future_value<\/b> value, we need to add 2 commas to keep the calculation accurate.<\/p>\n Here is my result. My monthly payment reduces to $906.89 per month.[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/07\/4.png” alt=”PMT formula 2″ title_text=”4″ align=”center” _builder_version=”4.17.4″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now let\u2019s say I want a balance of $5,000 leftover from my last monthly payment.<\/p>\n First, let\u2019s add it to the spreadsheet.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/07\/3.png” alt=”PMT formula 3″ title_text=”3″ align=”center” _builder_version=”4.17.4″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The PMT formula will then look like this:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=PMT(A2\/12,A3,A4,A5)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Here is my result. The monthly payment is larger at $1066.43.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/07\/2.png” alt=”PMT formula 4″ title_text=”2″ align=”center” _builder_version=”4.17.4″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n There are 2 other formulas that are closely linked to the PMT formula, in order to help us learn more about our loans or investments:<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In short, these two formulas can help to calculate the two aspects of your monthly payments (principal and interest) that have been calculated using the PMT.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n And there you have it! Now that you know how to calculate monthly payments for loans and investments, it may be a good idea to learn about PMT\u2019s related formulas to get the most out of your long-term payments.<\/p>\n Why not take a look at our article on How to use the PPMT formula in Google Sheets<\/a>?<\/p>\n Alternatively, discover some related articles below!<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" Nowadays, long-term payments are becoming more and more popular in both personal and professional environments. Suppose you need to pay back a loan from an initial business investment, or you are investing parts of your income on a regular basis. It\u2019s wise to have a good idea of the final amount you\u2019ll pay at the […]<\/p>\n","protected":false},"author":40,"featured_media":38076,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"Most of us like to invest part of our incomes on a periodic basis. And we base that on a financial goal of accumulating certain lumpsum at the end of our investment endeavor. So, we have a total amount in mind, and the number of periods we would like to invest. Is there a way to calculate the constant and periodic payment amount that leads to the total amount we just spoke about? Fortunately, we have the PMT<\/a> formula in Google Sheets that can do it for us.\nWhy use the PMT formula?<\/h2>\n
\n
What is the PMT formula?<\/h2>\n
Syntax<\/h3>\n
\n
\n
\n> If your installments are monthly, then you must divide the annual interest rate by 12 to get the \u2018monthly\u2019 rate<\/b>. [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/run-automatically-connect-sheet-icons.webp\"){kind=icon}
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\n
<\/span><\/p>\nHow to use the PMT formula in Google Sheets<\/h3>\n
The PPMT and IPMT formulas<\/h4>\n
\n
PMT formula Google Sheets<\/h3>\n
Syntax<\/h3>\nPMT(rate, number_of_periods, present_value, [future_value, end_or_beginning])<\/strong>\n
\n \t
Usage: PMT formula in Google Sheets<\/h3>\nNow that we learned the concept, let us apply our newfound knowledge directly on the Google Sheets application. Because examples make our learning process easier and faster. Please consider the screenshot below.\n\n
![\"PMT](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2018\/05\/PMT-formula-Illustration-Frame-2.png\")
\n\nOn the first formula (row # 2), we calculated the periodic payment using only the first three parameters while ignoring the last two. It calculated the payment amount that the investor has to pay for 60 periods to reach a lump sum of 10000 at 1.25% interest rate.\n\nUsing the second formula, we calculated the periodic payment amount towards a targeted amount. Which is equivalent to 10000 in present value plus 4000 in future value (after 60 periods). Which is why the payment value is higher than that of the first formula.\nNote<\/h4>\nThe first and the third formulas look essentially the same, but with different output values. Because there is one fundamental difference. In the first formula, we ignored the final parameter. Therefore, we inherently indicated that we are making the payment at the end of the period. Whereas in the last formula, we specified that we are paying at the beginning of the payment period.","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-8388","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/8388","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/comments?post=8388"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/8388\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/38076"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=8388"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=8388"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=8388"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}