I'm stuck with trying to find out what is the annuity scheduled balance of any period *without recurrent formula* that uses previous payment data. (Yes, I know that this way it won't be exact, but it's OK with me.)
The data is:
Annual interest rate is R, and it's relative part (R/12) is used every month for a annuity payment. The loan was taken for S dollars for N*12 months.
The loan was payed M (which is less than N) periods.
What is the balance B (the one that could be payed to the bank without a fee or fine, if agreed at advance).
I'd like to get the answer in MS Excel or Google spreadsheet functions' terms, if it's possible.
P.S. The question is a part of comparison of private car leasing deal vs getting a bank load and selling the car after certain period.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
The formula: B(n+1) = (B(n)-P)*(1+R/12)
is wrong, because P here is not constant, it should be the principal part of the annuity payment.
I've found an example in the net.
It seems that the balance value I need is: B(m) = FV(R/12, m, P, S)
where P is annuity payment (including principal and interest) and is: P = PMT(R/12, N, S).
Business Accounts
Answer for Membership
by: Michael2009Posted on 2009-08-21 at 11:57:22ID: 25154729
Let's say payments are figured in before interest is calculated.
So for example, on the first month, we take the payment off the principal, then we calculate interest, and the result is the new balance. Let's call the monthly payment P.
In this case, the recursive formula is (bear with me, as subscripts are hard to write here):
B(n+1) = (Bn-P)*(1+R/12)
In other words, this month's balance is last month's minus the payment, times interest.
If this is the case, the formula for the balance after M months is:
(S-P)*(1+R/12)^M - P* [ (1+R/12)^M - (1+R/12) ] / (R/12)
I'm not using the variable N which you gave, because that needs to be used to figure the payment P. The way you do this depends on the bank and/or the person paying.
In Excel terms, simply replace all the letter variables in the equation with references to the cells that contain the values you need. Also, the square brackets need to be changed to parentheses.
So it would be something like:
(S-P)*(1+R/12)^M-P*((1+R/1