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:
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?
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
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
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!
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.
So in other words, $50K would create an infinite amount of years until it runs out?
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
Ah aiight. thanks guys!! Guess I ought to hone my mathematical skills some more!
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]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks