How to build a loan calculator with periodic drawdowns

[robwill100]

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.

[#2]

@robwill100 

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.

[#3]

@robwill100 

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.

[#4]

@mathetes 

Thanks for getting back to me so quickly.

1. You are correct that we will not be charging interest on the undrawn funds
   
   1. Drawdown 1 would have interest charged for 60 months
   2. Drawdown 2 would have interest charged for 48 months
   3. Drawdown 3 would have interest charged for 36 months
   4. 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

[#5]

@robwill100 

1. Drawdown 1 would have interest charged for 60 months
2. Drawdown 2 would have interest charged for 48 months
3. Drawdown 3 would have interest charged for 36 months
4. 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. 

[#6]

@robwill100 

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.

[#7]

Creating a loan calculator can be a complex task, especially when you're factoring in periodic drawdowns. Have you considered reaching out to a professional to assist you with this project? They may be able to provide the expertise you need to ensure that your loan calculator functions as intended. Additionally, there are many resources online, such as forums and online communities.