I need to build a report that can calculate the delinquent interest, principal, and escrows for a mortgage loan. I also need to be able to account for interest changes (for adjustable rate loans). In order to account for those changes, I really need to be able to build a full amortization schedule in the report.
I am looking into some options with temp tables, etc. in SQL Server; however, I want to make sure I've exhausted all my options in Crystal. I am working with XI R2.
To amortize a loan, I need to be able to determine how many payments will be made over the life of the loan (easy enough, a 30 year loan has 360 payments - and that number is stored in our database) and then, for each payment, I need to calculate how much of the payment would be applied to principal and to interest. This would typically be done by taking the interest rate divided by 12 to get the monthly rate, and then multiplying that by the current principal balance of the loan. For example (the first three items are data elements we are storing):
Original Principal Balance: $58,500
Monthly Payment: $507.98
Rate: 9.875%
Monthly Rate: 9.875/12 = 0.8229%
First Interest Payment: 0.008229*58,500 = $481.39
First Principal Payment: $507.98-$481.39 = $26.59
After that first payment, the principal balance would decrease to $58,473.41, so the second interest payment would be calculated the same way but using $58,473.41 instead of the original loan amount of $58,500.
As you can see, the amount of the payment applied to principal and interest will change each month. That's where things get tricky (or trickier). As the loan matures, the interest rate will change periodically and I need to be able to account for those changes as well (the new interest rates and their associated effective dates are stored in our database).
Any ideas?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.