[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

Coding/Producing a loan schedule, help?

This question is for those who know how to produce a amortization loan scehedule in code. If you know about validating code with excel that would be great, also.

I have three parameters: principal (original balance), interest rate, and terms (in months). I want to output the monthly payment. For that monthly payment, I can code it.
For example. P = 500, i = (8.25/100)/12, and n = 12 (months).
monthlypayment = P [(i(1 + i)^n  / ((1 + i)^n) - 1]
43.55 = monthlypayment.

However, I want to produce x = 1...12, and y = remaining principal until it equals 0 and cumulative interest rate.

The values of x and ya and yb, should be something like.
Month   BeginningBalance  Payment   Principal   Interest  CumulativePrincipal  CumulativeInterest EndingBalance

1      $500.00       $43.55       $40.11       $3.44       $40.11       $3.44
2      $459.89       $43.55       $40.39       $3.16       $80.50       $6.60
3      $419.50       $43.55       $40.67       $2.88       $121.17       $9.48
4      $378.83       $43.55       $40.95       $2.60       $162.12       $12.08
5      $337.88       $43.55       $41.23       $2.32       $203.35       $14.40
6      $296.65       $43.55       $41.51       $2.04       $244.86       $16.44
7      $255.14       $43.55       $41.80       $1.75       $286.66       $18.19
8      $213.34       $43.55       $42.08       $1.47       $328.74       $19.66
9      $171.26       $43.55       $42.37       $1.18       $371.11       $20.84
10      $128.89       $43.55       $42.66       $0.89       $413.77       $21.73
11      $86.23       $43.55       $42.96       $0.59       $456.73       $22.32
12      $43.27       $43.55       $43.25       $0.30       $499.98       $22.62


I don't the other columns just the current balance and cumulative interest, where x = numberOfMonths, y(a) = remaining principal and y(b) = cumulative interest.

I can program in Java/C++ but a pseduo-code would be fine.
0
identityless
Asked:
identityless
2 Solutions
 
Dave BrettCommented:
In Excel you would use PPMT and IPMT for the 12 periods to calc the interest and principal, see http://members.iinet.net.au/~brettdj/EE/identityless.xls

Cheers

Dave
0
 
identitylessAuthor Commented:
Yes, but do you know how to code it in Java? Or Pseudo-code?
0
 
GotNetCommented:
THe mathematical formula for calculating payments is as follows:



Formula for periodic payment calculations:

                                      PeriodInterestRate
  Payment  =  Principal * -----------------------------------------
                          (                     1                  )
                          (1  -  --------------------------------  )
                          (      (1 + PeriodInterestRate)^Periods  )


If you are looking at getting balances from each payment, do the following

Multiply the principle times the annual interest rate divided by 12 (assuming monthly payments) , - this gives you the amount going to interest for that payment.  Subract that amount from the payment (calculated above).  This gives you the amount of principal reduced in that payment.

do this interatively, for each month, reduce the principal by the amount reduced in the previous month, recalculate the interest for that month (should be less than the month before) and then cacluate the remainder as principal.  This loop will iterate until the total number of periods has been calculated.  If the calculation is correct, the final principal will be 0.

Hope this helps!

GotNet
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now