+ Reply to Thread
Results 1 to 9 of 9

=NPER Function results with #NUM! Error???

  1. #1
    Forum Contributor
    Join Date
    04-30-2013
    Location
    D.C.
    MS-Off Ver
    Excel 2010
    Posts
    192

    =NPER Function results with #NUM! Error???

    Hello Excellers,

    I am wondering why at $10,000 for B7 (PV) would turn the result to 6.64 with this formula (which is correct):

    B4: 8%
    B6: 2,000
    B7: 10,000
    B8: Beginning

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But if I change the B7 to 50,000, it changes to #NUM! error. It seems like it could not handle such big numbers.

    How may we fix this?

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: =NPER Function results with #NUM! Error???

    Because you will never pay off the loan with that rate, payment, and present value; the payment is less than the interest.
    Last edited by shg; 10-20-2013 at 02:23 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: =NPER Function results with #NUM! Error???

    It appears to me that you are using the NPER function to calculate payments on a loan. The use of NPER is to calculate the number of payments to reach an investment goal. The pv and pmt values would therefore be negative values as they are monies paid out and being paid out respectively. The fv has to be equal to or greater than the pv or you end up with negative payments. If you enter 10000 as the future value, you will see that 0 payments are to be made. If you enter a fv greater than the pv then you will have positive number of payments to be made.

    If you are calculating the payments on a loan, use the PMT function.

    See the examples in the Help files.

    In both cases, if the payments are monthly divide the interest rate by 12, if the payments are quarterly divide by 4.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Forum Contributor
    Join Date
    04-30-2013
    Location
    D.C.
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: =NPER Function results with #NUM! Error???

    Okay, my apologizes for not being that clear.

    What I meant was if I have $10,000 in my account and I plan to withdraw $2,000 a year with 8% interest, it will take the account 6.64 years before it runs out. I would like to figure the same thing for how long it would take the account with $50,000 to last with annual withdraw of $2,000 with 8% interest. up to $5,000,000 as well. Should =NPER function be used for this or write a stand-alone formula for this?

    Hope this clarifies my original question!

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: =NPER Function results with #NUM! Error???

    I thought it was clear.

    With $50K in the bank earning 8% interest, you get $4K/year in interest. Withdrawing $2K per year, the principal goes up year over year, not down.

  6. #6
    Forum Contributor
    Join Date
    04-30-2013
    Location
    D.C.
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: =NPER Function results with #NUM! Error???

    So in other words, $50K would create an infinite amount of years until it runs out?

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: =NPER Function results with #NUM! Error???

    I believe what you are wanting is a "Sinking Fund" calculation. Unfortunately, Excel doesn't have a function for that that I could find.

    Your initial problem with 10000 withdrawing 2000 per annum after the first year would take nearly 7 years to exhaust.

    SHG is correct about the 50000. You would be taking out less than half the interest in the first year and the difference grows from there. I calculate that after 10 years, you would have approximately 79,000

  8. #8
    Forum Contributor
    Join Date
    04-30-2013
    Location
    D.C.
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: =NPER Function results with #NUM! Error???

    Ah aiight. thanks guys!! Guess I ought to hone my mathematical skills some more!

  9. #9
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: =NPER Function results with #NUM! Error???

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. PMT vs NPER
    By nl908 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-05-2013, 12:06 PM
  2. [SOLVED] Error in IF function - miscategorising results
    By Glayva in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-12-2012, 08:54 AM
  3. nper function calculation
    By bigfella in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2006, 10:05 AM
  4. [SOLVED] Calculating NPER
    By rick wintomac in forum Excel General
    Replies: 2
    Last Post: 04-09-2006, 12:10 AM
  5. [SOLVED] NPER - Retirement Example
    By mschumacker in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2005, 05:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1