Share via

Help with NPER Formula in Excel

Anonymous
2020-10-04T23:14:50+00:00

I keep getting a #NUM! when using NPER Formula to try to calculate the future value of an investment. I am solving for number of payments. The problem that the text is asking students to solve is as follows: The average US household credit card debt is $10,000, with average APR of 13% compounded monthly. If the average household made payments of $400 each month, how many months would it take to repay the credit card? 

I am using Excel's NPER function to solve the problem; however, I keep getting the #NUM! error. I usually see an answer populate next to the = below the value boxes. The only way I can get a value to appear next to the = is if I do not put a value any larger than 100 in the FV box. Once I add another zero to say make the value 10000, no answer will populate, thus entering the correct number of 10,000 will not work either. Is there something I need to correct in Excel to get this to work?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-10-06T04:11:55+00:00

    I wrote:

    Or was Dana's response not visible to you on your server?  Do IAs use a server that displays only responses from IAs?(!)  (I think someone conjectured that long ago.)

    JP wrote:

    Hi Joeu, FWIW, I've read on the Dutch forum that the IAS don't see the input/replies from other members, only the question and feedback from the OP.

    Thanks.  I suspect you mentioned that before, and that is the "conjecture" that I remember.

    IMHO, that would be terrible, if true, especially if I'm correct about how IAs get paid.

    It would be great if JenL would confirm that.  OTOH, if you're right, she doesn't see my question(!).

    (FYI, I just logged on to delete my first response.  But since you had a constructive response, I'll wait to see if someone provides additional information.)

    Hi gentlemen,

    I'm sorry but I didn't see the replies on my server until I checked the forum. My bad...

    Well, I definitely agree Dana's answer was correct. I have edited my response to give her the credit since she's the first one who replied with the correct answer. 

    Thank you guys for correcting and I hope you're all safe and healthy!

    All the Best,

    Jen :)

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2020-10-05T09:04:54+00:00

    Hi Joeu,

    FWIW, I've read on the Dutch forum that the IAS don't see the input/replies from other members, only the question and feedback from the OP.

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Anonymous
    2020-10-05T02:46:51+00:00

    Hello MBA! I'm Jen, an independent advisor and a Microsoft user like you. I'd be happy to help you out with this issue.

    Dana is correct - Since the rate of interest is only entered manually and not formatted as percentage, you have to express it in decimal.  Try to input .13/12 in rate, since 13%, when expressed in decimal is .13. Kindly mark her response as "answer" if you think this helped you.

    Just additional information -- when using NPER formula in Excel, #NUM! error occurs if the stated future value will never be met or the supplied periodic interest rate and payments are insufficient. In such a scenario, we will need to decrease the payment amount or increase the interest rate to achieve a valid result. 

    Stay safe and healthy!

    Regards,

    Jen :)

    0 comments
  5. Anonymous
    2020-10-05T01:51:01+00:00

    Hi.   13%    is either 13%, or 0.13 

    Rate = 13%/12

    or

    Rate = 0.13/12

    0 comments