## 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 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.

- mathetesSilver Contributor
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__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?**has been**borrowedOut of curiosity, I'm doing some playing with this...... but please answer my question above.

- robwill100Brass Contributor
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

- mathetesSilver Contributor
*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

(!) just underscores the fact that I'm a total novice with Excel's financial functions.a**ARE****finance**companyThanks 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

- mathetesSilver Contributor
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......

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.__Bottom line:__As I said, I'd like a reality check from someone who knows what they're doing.

- YahayaYusufuCopper Contributor
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.