Forum Discussion
How to build a loan calculator with periodic drawdowns
Thanks for getting back to me so quickly.
- You are correct that we will not be charging interest on the undrawn funds
- Drawdown 1 would have interest charged for 60 months
- Drawdown 2 would have interest charged for 48 months
- Drawdown 3 would have interest charged for 36 months
- Drawdown 4 would have interest charged for 24 months
The background to this is that we are a finance company and one of our partners has asked us to look at building a product whereby we would finance IT maintenance services over 5 years and provide a flat pmt schedule to avoid the customer getting increases each year.
Given that most of the services will be delivered in the future, we don't want to pay our partner all the $60K now, we only want to pay at the start of the year the services will be performed. By doing this we avoid the partner incurring accounting issues in how to manage to account for future revenue, but more importantly, we limit our credit risk and make sure the customer won't have paid for a bunch of services they haven't received or may not receive if the provider went broke.
Hope this makes sense
Here's another iteration, one with which I feel more confident. The final result is in a green box at the top, and I think I've done it--assuming these calculations pass CPA muster--in such a way that you can change the total loan, change the interest rate, and the sheet will still calculate. The 60 months overall period is built in (but readily changed if that level of flexibility becomes attractive).
Here's the bottom line.
Basically I get there by calculating monthly interest payments on
- $24,000 for 60 months
- $12,000 for 48 months
- $12,000 for 36 months
- $12,000 for 24 months
Then
- Adding all those monthly payments to get $11,835.13
- Evening that out monthly to $197.25 for each of 60 months
- Adding to that $1,000 (paying down the total 60,000 on a flat monthly basis)*
*It's that very last line that I'm the least confident in from a "legitimate finance" perspective.
So let me know what you people who ARE a finance company think. Would this model be legitimate? Would it serve your interests as well as those of your partners, those with whom you do business?
For those interested in the formula I use to calculate interest, here it is. I wrote it so that the identical formula could be used for each of the four columns, using absolute and relative references judiciously so as to refer to the necessary variables arrayed across the top and down the left.
=IFERROR(IPMT($B$7/12,B$14-$A19,B$14,-B$12,,1),0)
It's a lot easier to follow if you just open up the attached spreadsheet.