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,

Thanks for visiting Microsoft Community.

To address your requirements, assuming we calculate daily interest rates at an annual rate of 10% which accumulates over time, let's illustrate this with the example of loans taken on July 20th and July 30th. Here's a structured approach:

1. Data Layout: Organize your data into four columns: Date, Withdrawal, Cumulative Withdrawal, and Accumulated Interest.
2. Cumulative Loan Calculation: For the Cumulative Withdrawal, use a formula where each row sums up all amounts borrowed up to that point. In Excel terms, for cell B2, the formula would be =SUM($B$2:B2), and for B3, =SUM($B$2:B3), continuing this pattern down the column.
3. Interest Accrual: The interest calculated is compounded daily up to the current date. For instance, the accumulated interest up to July 10th would be calculated in cell D3 with a formula like =(A3-A2)*C2*0.1/365+D2. Similarly, for July 20th (assuming the first loan), it would be (A4-A3)*C3*0.1/365+D3.

I hope this guidance is helpful. Please feel free to reply with any further questions or concerns you may have.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist

Answer 2

Thank you, this has been most helpful, but I have some questions.

As you can see below, I used your formula to calculate a 5 month loan. Accordingly, the accrued interest comes out to $209.59, however, if I do the math in a mortgage calculator, the same 5 month period would come out to $211.83 in interest

Date	Amount	Running balance	Accrued interest
7/1/2024	$     5,000.00	$        5,000.00
12/1/2024	$                 -	$        5,000.00	$          209.59

Month	Deposit	Interest	Ending balance
1	$5,000.00	$41.67	$5,041.67
2	$0.00	$42.01	$5,083.68
3	$0.00	$42.36	$5,126.04
4	$0.00	$42.72	$5,168.76
5	$0.00	$43.07	$5,211.83

Answer 3

Here's another example

Answer 4

It actually works out great.

Another question - How do I input a payment made on account? For example, lets say he pays me back $1000 on 11/1/24, where & how would I input that so that it can recalculate all the figures?

Thanks

Answer 5

Hi Emelbee,

Thank you for your response.

From the screenshot you provided, one reason for the slight discrepancy in the interest calculation on the principal of $5000 after five months is that my initial response always used the original principal as the base for interest calculation, without considering the reinvestment of monthly interest into the principal for subsequent month's interest computation. If you wish to incorporate this logic, you can slightly modify the formula. For instance, you could change the formula for cell C3 to =SUM($B$2:B3) + D3, and then extend this configuration down to the following rows, as shown below:

 

As you can see, the result still does not match the calculation from the loan calculator, suggesting there might be specific nuances in the interest calculation logic that differ. As I am not a specialist in this field, I can only offer advice on the use of Excel formulas. You may refer to my response and adjust the formulas related to interest calculation according to your own needs.

Thank you for your understanding! If you find my response helpful, please take a moment to click "Yes" or "No" under my reply, which will assist other users with similar questions in finding answers more quickly.

Wishing you a pleasant day ahead.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist