{"id":431474,"date":"2022-04-13T18:00:02","date_gmt":"2022-04-13T15:00:02","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-calculate-a-loan-payment-interest-or-term-in-excel\/"},"modified":"2022-04-13T18:00:02","modified_gmt":"2022-04-13T15:00:02","slug":"how-to-calculate-a-loan-payment-interest-or-term-in-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-calculate-a-loan-payment-interest-or-term-in-excel\/","title":{"rendered":"#How to Calculate a Loan Payment, Interest, or Term in Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a38d4e1ed2dd\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a38d4e1ed2dd\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-calculate-a-loan-payment-interest-or-term-in-excel\/#%E2%80%9CHow_to_Calculate_a_Loan_Payment_Interest_or_Term_in_Excel%E2%80%9D\" >&#8220;How to Calculate a Loan Payment, Interest, or Term in Excel&#8221;<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-calculate-a-loan-payment-interest-or-term-in-excel\/#Calculate_a_Loan_Payment_in_Excel\" >Calculate a Loan Payment in Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-calculate-a-loan-payment-interest-or-term-in-excel\/#Formula_to_Calculate_an_Interest_Rate_in_Excel\" >Formula to Calculate an Interest Rate in Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-calculate-a-loan-payment-interest-or-term-in-excel\/#How_to_Calculate_a_Payment_Term_in_Excel\" >How to Calculate a Payment Term in Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-calculate-a-loan-payment-interest-or-term-in-excel\/#Optional_Arguments_for_Loan_Calculations\" >Optional Arguments for Loan Calculations<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Calculate_a_Loan_Payment_Interest_or_Term_in_Excel%E2%80%9D\"><\/span>&#8220;How to Calculate a Loan Payment, Interest, or Term in Excel&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-757715\" data-pagespeed-lazy-srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 400w, https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 1200w\" sizes=\"auto, 400w, 1200w\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel logo on a green background\" width=\"1200\" height=\"675\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Because of its functions and features, Excel is a great <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>lication for budgeting along with managing your money. If you already use it for finances, make your spreadsheet even more effective by calculating loan elements like payments, interest, or terms.<\/p>\n<p>Maybe you are contemplating a new car loan and want to know the payment ahead of time. You can use Excel to adjust the interest rate and payment term to see what you can afford. At the same time, you may have payment information on a current loan and want to see your interest rate or payment term.<\/p>\n<p>With a few simple functions and your data, you can easily get basic loan calculations in Microsoft Excel.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Calculate_a_Loan_Payment_in_Excel\"><\/span>Calculate a Loan Payment in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>For many people, affording a new car involves knowing what the monthly payment will be. To find out in Excel, you simply need the basic loan information and <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/765624\/basic-excel-functions-everybody-should-know\/\">a handy function.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>7 Essential Microsoft Excel Functions for Budgeting<\/em><\/strong><\/p>\n<p>Get the annual interest rate, number of payments you\u2019d like, and total loan amount and enter these into your sheet. Select the cell where you want to calculate the monthly payment; this is where you\u2019ll insert the PMT (payment) function.<\/p>\n<p>The syntax for the function is <code>PMT(rate, number_payments, loan_amount, future_value, type)<\/code>. The only required arguments are the first three for interest rate, number of payments, and loan amount.<\/p>\n<p>To get the monthly payment amount for a loan with four percent interest, 48 payments, and an amount of $20,000, you would use this formula:<\/p>\n<pre>=PMT(B2\/12,B3,B4)<\/pre>\n<p>As you see here, the interest rate is in cell B2 and we divide that by 12 to obtain the monthly interest. Then, the number of payments is in cell B3 and loan amount in cell B4.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793966\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/FindPayment-ExcelLoanCalculations.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"PMT function in Excel\" width=\"480\" height=\"280\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>By making slight adjustments to the constants, you can see what your payment would be if you had a different interest rate, made more or fewer payments, or changed the loan amount. When you adjust these figures, the formula updates automatically.<\/p>\n<p>For example, maybe the monthly payment is more than you can afford. By increasing the number of payments, you can see how much the monthly payments decrease.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793965\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/FindPaymentIncreaseTerm-ExcelLoanCalculations.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Adjust the terms to change the payment amount\" width=\"380\" height=\"230\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Calculate Percent Increases in Excel<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Formula_to_Calculate_an_Interest_Rate_in_Excel\"><\/span>Formula to Calculate an Interest Rate in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Maybe you have an existing loan and want to quickly see the annual interest rate you\u2019re paying. As simple as calculating a payment with basic loan details, you can do the same to determine the interest rate.<\/p>\n<p>Get the loan term, monthly payment, and loan amount and enter them in your sheet. Select the cell where you want to see the interest rate. You\u2019ll then enter the formula for the RATE function.<\/p>\n<p>The syntax for the function is <code>RATE(term, payment, loan_balance, future_value, type)<\/code> where the first three arguments are required for the term (in months or years as explained below), payment amount, and loan balance.<\/p>\n<p>Using the same example as above, we have the term as 48 months with the monthly payment as $451.58 and the loan amount as $20,000. You would use this formula:<\/p>\n<pre>=RATE(E2,E3,E4)*12<\/pre>\n<p>Here, the details are in order in the corresponding cells in the formula. We add <code>*12<\/code> at the end because we want the <em>annual<\/em> interest rate (12 months).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793967\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/FindInterestMonths-ExcelLoanCalculations.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"RATE function in Excel using months\" width=\"490\" height=\"280\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You can also enter the loan term in years instead of months and adjust the formula as follows:<\/p>\n<pre>=RATE(E2*12,E3,E4)*12<\/pre>\n<p>The <code>E2*12<\/code> portion multiples the number of years in cell E2 by 12 for the number of months in the term.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793968\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/FindInterestYears-ExcelLoanCalculations.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"RATE function in Excel using years\" width=\"520\" height=\"280\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"How_to_Calculate_a_Payment_Term_in_Excel\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"autotoc_anchor_2\">How to Calculate a Payment Term in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>One more handy loan calculation that can help you out is determining the payment period. You can see the number of months for a loan depending on the details.<\/p>\n<p>Gather the annual interest rate, monthly payment, and loan amount and place them in your sheet. Select the cell where you want to see the term and then use the NPER function to find the payment period.<\/p>\n<p>The syntax for the function is <code>NPER(rate, payment, loan_amount, future_value, type)<\/code> where the first three arguments are required for rate, payment, and loan amount.<\/p>\n<p>To use our same example, we have an annual interest rate of four percent, a payment of $451.58, and a loan amount of $20,000. Then, use this formula:<\/p>\n<pre>=NPER(H2\/12,H3,H4)<\/pre>\n<p>Cell H2 contains our interest rate and because it\u2019s the annual rate we <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/785845\/how-to-divide-numbers-in-microsoft-excel\/\">divide it by 12. Then, H3 and H4 contain the other details.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-793969\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/FindTerm-ExcelLoanCalculations.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"NPER function in Excel\" width=\"490\" height=\"280\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Optional_Arguments_for_Loan_Calculations\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"autotoc_anchor_3\">Optional Arguments for Loan Calculations<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>As mentioned with each function above, the <code>future_value<\/code> and <code>type<\/code> arguments are optional. Here\u2019s a brief explanation of each if you\u2019d like to include them in your formula.<\/p>\n<p><strong>Future Value<\/strong>: The amount you want after the final payment is made. Since this is assumed to be zero because you are paying an amount you owe, we omitted the argument. This may be a helpful argument to use in a formula for calculating an investment rather than a loan.<\/p>\n<p><strong>Type<\/strong>: This indicates when payments are due and is either 0 for the end of a period or 1 for the beginning of a period. If the argument is omitted, the function uses 0 by default.<\/p>\n<p>You can probably find a <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.reviewgeek.com\/48336\/google-wants-to-help-you-buy-a-home-or-refinance-your-loan\/\">loan calculator<\/a> with a Google search or even on your lender\u2019s website. But if you want to do some calculations in your own financial workbook or budget spreadsheet, these functions and formulas make it easy.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>What Is Money in Excel, and How Do You Get Started?<\/em><\/strong><\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/793953\/calculate-loan-payment-interest-term-in-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Calculate a Loan Payment, Interest, or Term in Excel&#8221; Because of its functions and features, Excel is a great application for budgeting along with managing your money. If you already use it for finances, make your spreadsheet even more effective by calculating loan elements like payments, interest, or terms. Maybe you are contemplating&#8230;<\/p>\n","protected":false},"author":1,"featured_media":431475,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-431474","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/431474","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=431474"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/431474\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/431475"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=431474"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=431474"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=431474"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}