Excel interest formula

Question

Hi,

I'm trying to set up an excel sheet with a formula that would calculate interest on an investment.

For example -

I extend an open line of construction credit to my friend in the amount of $50,000 for him to renovate his house. He will pay me back when he's done with construction.

On July 1 he draws $5000. On July 10th he draws $4000 and so on. Every time he needs more money he draws, just like a construction loan.

I need an excel sheet that would tabulate at a 10% interest rate.

Any ideas?

Thanks

Answer 1

Hi Emelbee,

Thank you for your response.

As per your request, we can introduce two new columns before the Accrued Interest column, namely, Payment and Cumulative Payment. In the Cumulative Payment column, for instance, cell E2 would be calculated as =SUM($D$2:D2), and this formula would be dragged down for subsequent rows.

Subsequently, we need to adjust the logic for the Running Balance column. Instead of simply summing up the values, it should now subtract the Cumulative Payment. For example, cell C2 would be calculated as =SUM($B$2:B2) + F2 - E2, and this formula would be dragged down for subsequent rows.

I hope this explanation is clear and meets your requirements. If you find my response helpful, please take a moment to click "Yes" or "No" under my reply. If you have any further questions or need additional assistance, please don't hesitate to ask.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist

Answer 2

Hi,

This is great!

Now, let's say he wants to pay back interest only, every couple of months. Would the formula be any different or would I just input the amount paid as usual?

Also, let's say he wants to pay up in full, what is the full amount owed at this point?

Answer 3

Hi,

Following up on the above question.

Thanks