By Admin on Jul 2, 2009 in Mathematics
rena_kurama asked:
Ok so I have to do an amortization table by hand now I know how to use the TVM solver on my calculator…but what do you do after that? So you have to future value the interest and everything but then how do you use it to fill the table? Im sorry if this question seems juvenille but I would really appreciate some help. Thank you
http://1mortgagecalculator.info
Ok so I have to do an amortization table by hand now I know how to use the TVM solver on my calculator…but what do you do after that? So you have to future value the interest and everything but then how do you use it to fill the table? Im sorry if this question seems juvenille but I would really appreciate some help. Thank you
http://1mortgagecalculator.info
Copyright
Let v = 1/(1+i). Let’s look at this very generally, where i is the interest rate over 1 period, which could be a year, or, more commonly, a month. If monthly, then i typically = some annual rate / 12. There are a lot of ways to handle nominal rates and compounding, so I’ll just try to keep it simple.
Suppose that the original principal is A and there will be n payments beginning in 1 period (such as 360 payments monthly).
The payment due, let’s call it P is the principal divided by the PV of the payments:
A = P x (sum from t=1 to n of v^t)
P = A / (sum from t=1 to n of v^t)
So far, this should look very basic.
Now, how much will still be owed after the kth payment?
It’s simply P x (sum from t=1 to n-k of v^t)
Note that this is the same expression as for A above, except that we’ve cut off the last k terms of the summation. That’s the portion of the loan that’s been amortized.
After one more payment, the remaining loan will be
P x (sum from t=1 to n-k-1 of v^t). The amount of the loan paid off, or amortized, in that payment was v^(n-k).
So the amount amortized starts small, and increases by a factor of (1+i) from one payment to the next. The amount of interest paid in the very first payment is the initial amount, A, times i. The amount of the loan paid off in the first payment is P-iA. the amount of interest paid in the 2nd payment will be
i (P-iA) etc. You can set up a recursive formula on a hand calculated spreadsheet, Loan Amount at beg. of period, – i times that is the intererst amount. Subtract that from the constant payment to get principal paid off, subtract that from the initial loan balance to get the end of period balance to be carried over to the next period. Here’s what that looks like in Excel:
Loan = $100010 years of payments annually
int = 10%
beg princintpaymentprinc paidend princ
1000.00100.00162.7562.75937.25
937.2593.73162.7569.02868.23
868.2386.82162.7575.92792.31
792.3179.23162.7583.51708.80
708.8070.88162.7591.87616.93
616.9361.69162.75101.05515.88
515.8851.59162.75111.16404.72
404.7240.47162.75122.27282.45
282.4528.25162.75134.50147.95
147.9514.80162.75147.950.00
I’m not sure what you need to put into your table, but if you can reproduce the numbers above using you calculator for a 10 year loan at 10%, then you got it.
freond1 | Jul 5, 2009 | Reply