Sep 14 2022 06:22 PM
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 periodic drawdowns during the term of the loan.
By way of example, the loan amount would be $60K over a 5-year period.
On day 1 of the loan, the amount borrowed would be $24K
At the end of year 1, the loan would increase by $12K
At the end of year 2, the loan would increase by $12K
At the end of year 3, the loan would increase by $12K
At the end of year 4, the loan would increase by $12K
By the end of the term the total loan amount would be $60K
To avoid yearly increases in the payments, I want the calculator to work out what the 60 equal monthly pmts would be.
Note, the loan term, interest rates and annual drawdowns are fixed.
I hope I have clearly articulated the request.
Sep 14 2022 06:38 PM
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.
Sep 14 2022 07:07 PM
Here's a possible answer (pending your answer to my question above; I assumed it went they way I was thinking it should: interest only on the amount actually borrowed up to that point).
I have no idea whether this is even reasonable, what a finance expert would think. There's one basic interest payment function in here in column D; all the others just add. I charge interest only the amount borrowed (see column C)....
Anyway, it needs a reality check, but seems possible. As I said, I did it more for sake of satisfying my curiosity, so I'll be very interested in what a CPA or CFA might have to say......
Bottom line: I came up with a monthly of $1,172.76 that would repay the 60K over five years. A total of $70,365.7 would be paid in 60 equal payments.
As I said, I'd like a reality check from someone who knows what they're doing.
Sep 14 2022 09:01 PM
Thanks for getting back to me so quickly.
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
Sep 15 2022 05:26 AM
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.
Sep 15 2022 06:59 AM
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
Then
*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.