Need DB Template/VBA for Loan Amortization and Payment Tracking

[ItsMe]

I need to keep track of a few loans and I am in need of a solution that is from the Bank's perspective. Does anybody know of some custom functions or even a template that I could use to keep track of payments, principal, interest, etc.?



I need it to work on a 365 day year APR compounded daily. I suppose editing a monthly to work as daily would not be a huge thing. But, I certainly need to keep rounding accurate.

Anyway, thought I would throw it out there.

[aytee111]

Re-inventing the wheel? There must be loads of off-the-shelf packages that do this.

[ItsMe]

I thought I would give this thread a Saturday bump in case someone had something to contribute.

[rpeare]

This might actually be fun to develop but I don't have any custom functions to do what you're after. Amoritization schedules compounded on a daily (fractional) APR rate should not be that difficult to figure out as long as you have a formula to follow but the real fun would be trying to do the same thing with variable rate loans. Why though would a bank need a custom solution when even a local bank would be able to afford software to handle it.

[ItsMe]

Yeah, I did find a couple examples out there on youtube. I believe they rely on the PMT() function and the PPMT() function.
https://msdn.microsoft.com/en-us/lib.../gg264126.aspx
ppmt
https://msdn.microsoft.com/en-us/lib.../gg264698.aspx

So, now I am wondering if it is going to be easier to start from scratch and use this built in function or look at a couple of other examples. One example explains they implemented interest compounded daily. But, when I look at the PMT() function, it seems everything is managed with a floating point Double. With this variable, it is determining the payment based on compounding monthly or at least compounding interest on each payment/installment anniversary.

My dilemma is do I provide as an argument all of the days for the entire term or all of the months for the entire term? For example, if I supply all of the days, I may be able to multiply the result by the number of days in a given month to determine the monthly payment.

What I am trying to do is follow the business rules of a 365 day year at a specified APR. For small numbers, the above nuances would not cause an issue. For larger numbers (principal amounts), you may discover some missing pennies.

[rpeare]

I don't know why you'd compound interest daily unless these are short term loans where you would expect a payment every day, compounding interest usually occurs on the anniversary of the loan payment schedule (i.e. monthly for mortgages, weekly sometimes for payday loans or buy here pay here car sellers), the net effect would be to inflate the amount the user has to pay back (maybe that's the goal, I don't know)

Maybe try this
http://www.exceluser.com/formulas/te...ortization.htm

Even if you don't use it it will give some good direction on how to figure the scheduling of payments etc.

[ItsMe]

I will check out the link. As for compounding daily, jumbo loans can illustrate improper rounding and compound techniques. The business rules, as defined in the agreement, are dictating the 365 day year. I am simply planning ahead to avoid painting myself in a corner.