How to calculate loan interest up to date in excel using formula?

Asked By Daniel Kaffe 10 points N/A Posted on - 08/04/2012

If you are an expert in excel, please solve my problem.

I have an excel sheet including loans given to many customers. Repayments should be done daily for these loans. The interest rate is 5% per month (30 days). If loans have been given in previous dates, How to find the total interest payable up to a current date. Can you tell me a formula?

• Status: Open
• Question Views: 3307
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Geisha Nitta

Go To Solution

Best Answer

Best Answer

Answered By Geisha Nitta 0 points N/A #136166

How to calculate loan interest up to date in excel using formula?

I will give you a solution to your problem using functions available in Microsoft excel. Please refer the below example.

Then type the heading in the Column D as “Current date” and Column E “Interest to date”.

Then enter the current date in the relevant column. I will type the date as 01/08/2012 and copy it to below the cells.

Now type the following formula in the column E.

=A3*C3/30*(D3-B3)

Now copy the same formula to below the cells. Then you will be able to get the result you expected as shown in the below image.

I believe this answer would be helpful to you in order to get the result.

About Geisha Nitta

Questions
1

Answers
55

Best Answers
27

• Vote Up 0 Vote Down

• Posted on - 08/04/2012
• Question Category: MS Excel

Answered By Anthew Staw 0 points N/A #136167

How to calculate loan interest up to date in excel using formula?

I have read your post and I got the formula you need. Based on the facts that you mentioned you want a formula that could calculate the interest rate of a loan automatically in terms of the loan date and the current date right? Here’s the formula:

=((DAYS360(loan_date,NOW(),0))/30)*(interest*loan_amount)+loan_amount

Key-in the formula and follow the pattern above say for example the ‘interest’ should be the cell for the interest rate of just type 5% in it. Hope this helps.

About Anthew Staw

Questions
3

Answers
34

Best Answers
3

• Vote Up 0 Vote Down

• Posted on - 08/05/2012
• Question Category: MS Excel