Forum Discussion
How to build a loan calculator with periodic drawdowns
Hi all and thank you for any assistance in advance. I am seeking help in creating a loan calculator that will show an equal flat monthly pmt over the term of the loan whilst allowing for fixed pe...
For what it's worth, I think your description here on the forum page added an extra "drawdown", one more than appears on your spreadsheet. You have it starting with 24K and increasing four times by 12K, for a total of 72K, not the 60K you had stated. It's easy to understand how it happened, since the whole thing is somewhat confusing.....
Anyway, on a serious note, one thing that's not clear to me: I assume interest is to be calculated at any given point in time prior to the final "drawdown" solely on the amount that has been borrowed up to that point. Said another way, you surely aren't expecting to charge interest on the full 60K when only 24 or 36 have been borrowed. That introduces a wrinkle here in the calculations, though, that makes coming up with 60 equal monthly payments quite tricky. What in fact are you expecting in this regard?
Out of curiosity, I'm doing some playing with this...... but please answer my question above.
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.
- 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
I rethought this over the night and realized this would have to be the case...so I'll be revising what I did last evening. However, seeing that you ARE a finance company (!) just underscores the fact that I'm a total novice with Excel's financial functions.
Thanks for the bigger picture....it doesn't change the complexities of setting this up in a way that is financially sound, but it helps (me, at any rate) to see how the system could be used, and to realize that it needs to be designed in such a way that the variables can be changed--the amount of each "drawdown", the interest rate being charged. I'm going to leave the five year (60 month) time frame alone as a fixed matter, but I could see how, if the product ends up working, you might get requests for similar packages over, ten years....
Anyway, more to come, with the proviso that I'm not really qualified and a CPA should be reviewing any product that comes from my keyboard.
- You are correct that we will not be charging interest on the undrawn funds
