Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Calculating interest and principle payments after a loan redraw (mid-loan withdrawal)

0

What formula or spreadsheet design should I use to calculate the new interest and principal amounts due after redrawing on a loan half way through the loan period?

eg. 50k loan @ 5%pa payable monthly over 5 years (60 periods), with a redraw of $5k one year in.

I currently use the simple =PMT(Rate,N,-Pv) to calulate monthly repayments over the 5 years of the loan period. I calculate daily interest accrued and subtract that from the monthly payment to give the principal and interest components.

This gives me a daily outstanding balance.

If I then withdraw an amount thereby increasing the outstanding balance, how do I calculate the new monthly repayment? Assuming the term of the loan does not change.

eg. after 12 mths of payments of $943.56 the loan has 48 months left and an outstanding balance of 41,909. I redraw $5,000. What formula do I use to calculate the new monthly repayments?

Thank you!

excelformulas-functionsfinance
MS Office Version: Rebecca
posted by: Rebecca on Thu Nov 16, 2017 at 10:09 pm
Answer
Discuss

Answers

0

Whichever way you handle it, a new loan will have to be given. That fact makes the matter more one to be decided by company policy than accounting methods.

With that said, you could simply make the new draw-down a separate loan to be repaid over the same remaining period as the original loan. For the monthly repayments just add the two items together.

More likely, the desire will be to hold the accounting to one loan (bad practice on top of bad practice because it changes the loan into an overdraft). In that case the old loan has to be closed down and the new loan calculated to include the remaining principal, the new drawdown and accrued interest, and the installments calculated on the remaining period or any other new period that was agreed.

posted by: Variatus (rep: 4889) on Thu Nov 16, 2017 at 11:56 pm
Discuss

Discussion

Thanks Variatus. Agreed on the accouting treatments.
In this case I am more concerned with formula in excel as this is not a formal loan with an institution but rather between individuals. My main concern is to predict the impact on my own repayments over time. I can make another totally separate locan calc though. Will try that.
Rebecca Nov 21, '17 at 9:12 pm
Yes. You would need to calculate a new loan amount with a new repayment schedule and apply the same formula you already know. That is what I recommended above. If you think that my answer might be helpful to others with a similar question, please mark the answer as "Selected". Thank you, and have a good day.
Variatus (rep: 4889) Nov 21, '17 at 9:40 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login