{"id":7865,"date":"2024-01-05T17:20:52","date_gmt":"2024-01-05T17:20:52","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=7865"},"modified":"2024-02-01T23:09:42","modified_gmt":"2024-02-01T23:09:42","slug":"nper-function-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/nper-function-in-excel\/","title":{"rendered":"NPER function in Excel with Examples"},"content":{"rendered":"<p>The NPER function in Excel, short for &#8220;Number of Periods,&#8221; is a powerful tool for financial analysis and planning. This function is especially valuable when it comes to making critical financial decisions, such as loan or investment planning. NPER allows users to determine the number of payment periods required to reach a specified financial goal, taking into account factors like interest rates, regular contributions or withdrawals, and the present value of an investment. Whether you&#8217;re working on retirement planning, evaluating a loan, or estimating the time needed to reach a savings target, understanding how to use the NPER function can simplify complex financial calculations and help you make informed decisions with confidence. In this guide, we will explore the ins and outs of the NPER function, demonstrating its practical applications and showing you how to use it effectively in various financial scenarios.<\/p>\n<p><strong>This content Covers:-<\/strong><\/p>\n<ol>\n<li><a href=\"#what-is-nper-function?\"><strong>What is NPER Function?<\/strong><\/a><\/li>\n<li><a href=\"#using-nper-function-in-excel-with-formula\"><strong>Using NPER function in Excel with formula<\/strong><\/a><\/li>\n<li><a href=\"#apply-nper-function-in-excel\"><strong>Apply NPER function in Excel<\/strong><\/a><\/li>\n<li><a href=\"#apply-basic-nper-formula-in-excel\"><strong>Apply Basic NPER formula in Excel<\/strong><\/a><\/li>\n<li><a href=\"#how-to-use-nper-function-in-excel-?\"><strong>How to use NPER function in Excel ?<\/strong><\/a><\/li>\n<\/ol>\n<ul>\n<li><strong>Calculate the number of periodic payments for a loan<\/strong><\/li>\n<li><strong>Calculate NPER based on present and future values<\/strong><\/li>\n<\/ul>\n<h2><strong id=\"what-is-nper-function?\">1. What is NPER Function?<\/strong><\/h2>\n<p>&#8220;NPER&#8221; stands for &#8220;Number of Periods.&#8221; Basically, It is based on a fixed interest rate, periodic payment amount\u00a0 and the current or future value of the loan or investment. NPER(Number of periods) helps you determine how long it will take to reach a specific financial goal or pay off debt by indicating the number of periods needed.<\/p>\n<p>This function is often used in financial planning, such as to calculate the term of a loan or the time needed to reach a savings goal.<\/p>\n<p>In the below example here is given, Loan amount, monthly payment and yearly interest rate, so calculate the Rate of Interest (ROI).<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" class=\"aligncenter size-full wp-image-7866\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-1.png\" alt=\"Formulas of Nper\" width=\"637\" height=\"169\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-1.png 637w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-1-300x80.png 300w\" sizes=\"(max-width: 637px) 100vw, 637px\" \/><\/p>\n<h2><strong id=\"using-nper-function-in-excel-with-formula\">2. Using NPER function in Excel with formula ?<\/strong><\/h2>\n<p>The NPER function uses the following arguments:<\/p>\n<p><strong>Interest rate (required) &#8211;<\/strong>\u00a0 Interest rate per period. If you make payments once a year, specify an annual interest rate; If you pay monthly, indicate the monthly interest rate, etc.<\/p>\n<p><strong>Pmt (required argument) \u2013<\/strong> The payment made each period. Typically, it includes principal and interest but no other fees or taxes.<\/p>\n<p><strong>Pv (required argument) \u2013<\/strong> The present value or gross amount that a series of future payments is currently worth. That is, the present value of\u00a0 a series of future cash flows.<\/p>\n<p><strong>Fv (optional argument) &#8211;<\/strong> This is the future value or\u00a0 cash balance\u00a0 we want at the end after the last payment. When omitted, it takes on a value of 0.<\/p>\n<p><strong>Type (optional argument) \u2013<\/strong> Indicates when payment is due. If type is set to 0 or omitted, payments are due at the end of the period.<\/p>\n<p>If the value is <strong>0<\/strong>,<\/p>\n<p>then payment is due at the beginning of the period.<\/p>\n<p>If the value is <strong>1<\/strong>,<\/p>\n<p>the beginning of the accounting period<\/p>\n<p><strong>Step 1:<\/strong> Insert the Data into your excel sheet.<\/p>\n<p>Entering the Data.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter size-full wp-image-7867\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-2.png\" alt=\"Formulas of Nper\" width=\"635\" height=\"156\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-2.png 635w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-2-300x74.png 300w\" sizes=\"(max-width: 635px) 100vw, 635px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Now, choose a column for getting the result of the rate of interest of the loan amount there.<\/p>\n<p>You can see a column A6 is selected below and named it time period for getting ROI(rate of interest).<\/p>\n<p><img decoding=\"async\" class=\"aligncenter size-full wp-image-7868\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-3.png\" alt=\"Formulas of Nper\" width=\"555\" height=\"215\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-3.png 555w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-3-300x116.png 300w\" sizes=\"(max-width: 555px) 100vw, 555px\" \/><\/p>\n<p><strong>Step 3:<\/strong> Now, insert the formula according to your information column.<\/p>\n<p>Formula<strong>: =Nper (D4\/12, C4,B4), <\/strong>in here interest rate is given yearly But the payment method is given monthly, so the interest rate turned into monthly. That\u2019s why the interest rate is divided by 12.<\/p>\n<figure id=\"attachment_7869\" aria-describedby=\"caption-attachment-7869\" style=\"width: 517px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-7869 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-4.png\" alt=\"NPER function\" width=\"517\" height=\"256\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-4.png 517w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-4-300x149.png 300w\" sizes=\"(max-width: 517px) 100vw, 517px\" \/><figcaption id=\"caption-attachment-7869\" class=\"wp-caption-text\">NPER function<\/figcaption><\/figure>\n<p><strong>Step 4:<\/strong> After entering the formula Press enter.<\/p>\n<p>Here is the result of rate of interest below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7870\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-5.png\" alt=\"Formulas of Nper\" width=\"514\" height=\"212\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-5.png 514w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-5-300x124.png 300w\" sizes=\"(max-width: 514px) 100vw, 514px\" \/><\/p>\n<h3><strong id=\"apply-nper-function-in-excel\">3. Apply NPER function in Excel<\/strong><\/h3>\n<p><strong>Step 1:<\/strong> First, take some information and put them into excel.<\/p>\n<p>Where,<\/p>\n<ul>\n<li>\u00a0Annual interest rate (<em>rate<\/em>) \u2013 7%<\/li>\n<li>Periodic payment (<em>pmt<\/em>) &#8211; -$1500<\/li>\n<li>Present value (<em>pv<\/em>) : -$20,000<\/li>\n<li>Future value (<em>fv<\/em>) \u2013 40,000<\/li>\n<li>When payments are due (<em>type<\/em>) &#8211; 1<\/li>\n<li>Periods per year \u2013 5<\/li>\n<li>What is the number of periods?<\/li>\n<\/ul>\n<p>Entered the above data into an Excel sheet.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7871\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-6.png\" alt=\"Formulas of Nper\" width=\"637\" height=\"219\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-6.png 637w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-6-300x103.png 300w\" sizes=\"(max-width: 637px) 100vw, 637px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Now, Choose any one column for extracting the results.<\/p>\n<p>Choosing A9 and B9 for receiving the result.<\/p>\n<figure id=\"attachment_7872\" aria-describedby=\"caption-attachment-7872\" style=\"width: 542px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-7872 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-7.png\" alt=\"NPER function\" width=\"542\" height=\"266\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-7.png 542w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-7-300x147.png 300w\" sizes=\"(max-width: 542px) 100vw, 542px\" \/><figcaption id=\"caption-attachment-7872\" class=\"wp-caption-text\">NPER function<\/figcaption><\/figure>\n<p><strong>Step 3:<\/strong> To get the exact periodic interest rate, the annual interest rate (C2) is divided by the number of periods in the year (C7).<\/p>\n<p>The formula is<strong>: =NPER(C2\/C7,C3,C4,C5,C6<\/strong>)<\/p>\n<p>Use the formula below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7873\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-8.png\" alt=\"Formulas of Nper\" width=\"587\" height=\"315\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-8.png 587w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-8-300x161.png 300w\" sizes=\"(max-width: 587px) 100vw, 587px\" \/><\/p>\n<p><strong>Step 4:<\/strong> When the formula is set, press enter and the result will come out.<\/p>\n<p>Here is the result below:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7874\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-9.png\" alt=\"Formulas of Nper\" width=\"526\" height=\"266\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-9.png 526w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-9-300x152.png 300w\" sizes=\"(max-width: 526px) 100vw, 526px\" \/><\/p>\n<h2><strong id=\"apply-basic-nper-formula-in-excel\">4. Apply Basic NPER formula in Excel<\/strong><\/h2>\n<p>Here are some basic steps to better understand Knope theory in its simplest form to get the number of loan payment periods based on the following data:<\/p>\n<p><strong>Step 1:<\/strong> Put this data in you excel as written.<\/p>\n<p>Annual interest rate (interest rate): 10%<\/p>\n<p>Annual payment (pmt): -$4600<\/p>\n<p>Loan amount (pv): $25,000<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7875\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-10.png\" alt=\"Formulas of Nper\" width=\"636\" height=\"195\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-10.png 636w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-10-300x92.png 300w\" sizes=\"(max-width: 636px) 100vw, 636px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Now, choose a column for getting the result of the rate of interest of the loan amount there.<\/p>\n<p>You can see a column A8 and B8 is selected below and named it Number of periods(Years) to get the result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7876\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-11.png\" alt=\"Formulas of Nper\" width=\"527\" height=\"259\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-11.png 527w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-11-300x147.png 300w\" sizes=\"(max-width: 527px) 100vw, 527px\" \/><\/p>\n<p><strong>Step 3:<\/strong> Now you can use this basic formula to calculate the number of periods.<\/p>\n<p><strong>=NPER(C4,C5,C6)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7877\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-12.png\" alt=\"Formulas of Nper\" width=\"544\" height=\"304\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-12.png 544w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-12-300x168.png 300w\" sizes=\"(max-width: 544px) 100vw, 544px\" \/><\/p>\n<p><strong>Step 4: <\/strong>After entering the formula, you need to press Enter. After pressing Enter you will get the results as shown below.<\/p>\n<p>In this case, the future value(FV) is not relevant and is ignored since the payment is due at the end of the year, which is the default type.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7878\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-13.png\" alt=\"Formulas of Nper\" width=\"527\" height=\"266\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-13.png 527w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-13-300x151.png 300w\" sizes=\"(max-width: 527px) 100vw, 527px\" \/><\/p>\n<p><strong>Step 5: <\/strong>Here, You are offered a loan of $25,000\u00a0 at 10% annual interest, and you\u00a0 pay $4,600 to the bank each year. This formula indicates that it will take eight payments per year to repay the loan.<\/p>\n<p>If you want to enter the recurring payment as a positive number, insert a minus sign directly before the PMT argument in the formula as the picture shows.<\/p>\n<p><strong>=NPER (C4,-C5,C6)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7879\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-14.png\" alt=\"Formulas of Nper\" width=\"636\" height=\"289\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-14.png 636w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-14-300x136.png 300w\" sizes=\"(max-width: 636px) 100vw, 636px\" \/><\/p>\n<h2><strong id=\"how-to-use-nper-function-in-excel-?\">5. How to use the NPER function in Excel? <\/strong><\/h2>\n<h3><strong>5.1 Calculate the number of periodic payments for a loan<\/strong><\/h3>\n<p>Most of the loans and long-term loans are repaid in monthly installments. Some are paid quarterly or semi-annually. For calculating this type of loan thing is to convert the annual interest rate to a periodic interest rate. You need to divide the annual percentage rate by the number of periods in the year. To do this, the formulas are:<\/p>\n<p>For, Monthly payments:\u00a0<em>rate<\/em>\u00a0= annual interest rate \/ 12<\/p>\n<p>For, Quarterly payments:\u00a0<em>rate<\/em>\u00a0= annual interest rate \/ 4<\/p>\n<p>For, Semiannual payments:\u00a0<em>rate<\/em>\u00a0= annual interest rate \/ 2<\/p>\n<p><strong>Example:<\/strong><\/p>\n<p>You can start by entering data into the cells following the underneath picture:<\/p>\n<p>Annual interest rate : 6%<\/p>\n<p>Yearly Payment (pmt): $700<\/p>\n<p>Loan amount (pv): $20,000<\/p>\n<p><strong>Step 1:<\/strong> Put the information into the table.<\/p>\n<p>Entered the information here.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7880\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-15.png\" alt=\"Formulas of Nper\" width=\"637\" height=\"198\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-15.png 637w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-15-300x93.png 300w\" sizes=\"(max-width: 637px) 100vw, 637px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Now, choose a column for getting the result of the rate of interest of the loan amount there.<\/p>\n<p>You can see a column A8 and B8 is selected below and named it Number of periods(Monthly) to get the result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7881\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-16.png\" alt=\"Formulas of Nper \" width=\"569\" height=\"261\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-16.png 569w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-16-300x138.png 300w\" sizes=\"(max-width: 569px) 100vw, 569px\" \/><\/p>\n<p><strong>Step 4: <\/strong>Divide C4 by 12 and follow the formula to calculate the number of monthly payments on the loan:<\/p>\n<p><strong>=NPER(C4\/12,C5,C6) <\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7882\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-17.png\" alt=\"Formulas of Nper\" width=\"588\" height=\"317\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-17.png 588w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-17-300x162.png 300w\" sizes=\"(max-width: 588px) 100vw, 588px\" \/><\/p>\n<p><strong>Step 5: <\/strong>Now press Enter and the result will come out.<\/p>\n<p>The result is outlined below. After seeing the result the photo says that it will take 30 months or (2 year 6 months) to pay off.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7883\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-18.png\" alt=\"Formulas of Nper\" width=\"573\" height=\"261\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-18.png 573w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-18-300x137.png 300w\" sizes=\"(max-width: 573px) 100vw, 573px\" \/><\/p>\n<p><strong>Step 6:<\/strong> For Quarterly, You can start by entering data into the cells following the underneath picture:<\/p>\n<p>Annual interest rate (C2): 8%<\/p>\n<p>Monthly Payment (Pmt): $1200<\/p>\n<p>Loan amount (Pv): $10,000<\/p>\n<p>What is the periods of number quarterly?<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7884\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-19.png\" alt=\"Formulas of Nper\" width=\"635\" height=\"202\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-19.png 635w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-19-300x95.png 300w\" sizes=\"(max-width: 635px) 100vw, 635px\" \/><\/p>\n<p><strong>Step 7:<\/strong> Now, choose a column for getting the result of the rate of interest of the loan amount there.<\/p>\n<p>You can see a column A8 and B8 is selected below and named it Number of periods(Quarterly) to get the result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7885\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-20.png\" alt=\"Formulas of Nper\" width=\"517\" height=\"263\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-20.png 517w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-20-300x153.png 300w\" sizes=\"(max-width: 517px) 100vw, 517px\" \/><\/p>\n<p><strong>Step 8:<\/strong> Using F4\/4 for\u00a0<em>rate<\/em>\u00a0for\u00a0 NPER to return periods in quarters. The formula is:<\/p>\n<p><strong>=NPER(C4\/4,C5,C6)<\/strong><\/p>\n<p>The formula is used below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7886\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-21.png\" alt=\"Formulas of Nper \" width=\"512\" height=\"314\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-21.png 512w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-21-300x184.png 300w\" sizes=\"(max-width: 512px) 100vw, 512px\" \/><\/p>\n<p><strong>Step 9:<\/strong> Now, press Enter and the answer will show.<\/p>\n<p>The result is outlined below:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7887\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-22.png\" alt=\"Formulas of Nper \" width=\"558\" height=\"257\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-22.png 558w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-22-300x138.png 300w\" sizes=\"(max-width: 558px) 100vw, 558px\" \/><\/p>\n<h3><strong>5.2 <\/strong><strong>Calculate NPER based on present and future values<\/strong><\/h3>\n<p><strong>Step 1:<\/strong> For calculating NPER based on present and future values, first pick some data and enter into the excel.<\/p>\n<p>Where,<\/p>\n<ul>\n<li>Annual interest rate : 6%<\/li>\n<li>Monthly payment (pmt) : -800<\/li>\n<li>Present value(pv) : -2,000<\/li>\n<li>Future value (Fv): 20,000<\/li>\n<li>Periods per year : 10<\/li>\n<li>Calculate the number of periods.<\/li>\n<\/ul>\n<p>Entered the datas.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7888\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-23.png\" alt=\"Formulas of Nper\" width=\"636\" height=\"233\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-23.png 636w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-23-300x110.png 300w\" sizes=\"(max-width: 636px) 100vw, 636px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Now, choose a column for getting the result of the rate of interest of the loan amount there.<\/p>\n<p>You can see a column A10 and B10 is selected below and named it Number of periods(Quarterly) to get the result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7889\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-24.png\" alt=\"Formulas of Nper \" width=\"533\" height=\"315\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-24.png 533w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-24-300x177.png 300w\" sizes=\"(max-width: 533px) 100vw, 533px\" \/><\/p>\n<p><strong>Step 3: <\/strong>For this the formula will be: <strong>=NPER(C4\/C6,C5,C6,C7<\/strong>)<\/p>\n<p>In the below picture, the formula is entered.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7890\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-25.png\" alt=\"Formulas of Nper \" width=\"536\" height=\"349\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-25.png 536w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-25-300x195.png 300w\" sizes=\"(max-width: 536px) 100vw, 536px\" \/><\/p>\n<p><strong>Step 4: <\/strong>The result is here and it is negative. That means the investment will not be profitable.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7891\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-26.png\" alt=\"Formulas of Nper\" width=\"531\" height=\"307\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-26.png 531w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2024\/01\/Formulas-of-Nper-26-300x173.png 300w\" sizes=\"(max-width: 531px) 100vw, 531px\" \/><\/p>\n<p>You may be interested:<\/p>\n<ol>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Finance\" target=\"_blank\" rel=\"noopener\">Financial Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Sales\" target=\"_blank\" rel=\"noopener\">Sales Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Human-Resource\" target=\"_blank\" rel=\"noopener\">HR Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.executiveknowledge.org\/dashboard-templates\/35\" target=\"_blank\" rel=\"noopener\">Data Visualization Charts<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>The NPER function in Excel, short for &#8220;Number of Periods,&#8221; is a powerful tool for financial analysis and planning. This function is especially valuable when it comes to making critical financial decisions, such as loan or investment planning. NPER allows users to determine the number of payment periods required to reach a specified financial goal, &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/www.bizinfograph.com\/resource\/nper-function-in-excel\/\"> <span class=\"screen-reader-text\">NPER function in Excel with Examples<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"categories":[876],"tags":[877],"class_list":["post-7865","post","type-post","status-publish","format-standard","hentry","category-nper-for-excel","tag-nper-for-excel"],"_links":{"self":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/7865","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/comments?post=7865"}],"version-history":[{"count":5,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/7865\/revisions"}],"predecessor-version":[{"id":8385,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/7865\/revisions\/8385"}],"wp:attachment":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/media?parent=7865"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/categories?post=7865"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/tags?post=7865"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}