Excel Formula to calculate interest based on dates

hbtitansfan,

Having played with this then I have come up with the following:

Origional Loan Amount in Cell A1 - 12400
Loan Date in cell A2 - 04/30/03
Interest Rate in cell A3 - 35%
Re-payments all in column B, one after another
Total outstanding on loan formula in cell C1:
=A1-CUMIPMT(A3/365,INT((NOW()-A2)/365),A1,1,INT((NOW()-A2)/365),1)-SUM(B:B)

Hope this helps!

Or, a different way of doing it:

Cell A1 = Start date = 04/30/03
Cell B1 = Interest = 35%
Cell C1 = Start Amount = 12400

Cell A2 - Ax = payment dates
Cell B2 - Bx = payment amounts
Cell C2 - Cx = remaining balance = formula:
=C1-CUMIPMT($B$1/365,INT(A1-B1),C1,1,INT(A1-B1),1)-B2

Then you can just add payment dates and amounts into column A and B and then just fill down the formula in column C.

> It is to be paid back at 35% interest rate.

There isn't enough information here - what is the term of the loan and how often are payments made?

Cheers

Dave

Lynn,

Like Dave said, some more specific inputs are necessary.

1. Is the repayment happening on a basis like EMI? (Equated Monthly Instalments)
    If yes, what is the term of repayment? If no, then do you want to have a column of
    how much has been repaid so far? And split that into Principal and Interest?

2. If the repayment is on EMI, what are the "rests"? Is the interest on reducing balance?

Once the requirement is clear the solution should be simple, and within Excel's
built-in functions.

I hope I am not complicating things more than the original question :-)

- stochastic

There are no regular installment payments and there is no end date for when the loans have to be paid back.  It is paid back at random which is why we need to know on a daily basis what the accrued interest is according to the different dates when the loans were made.  Like I said there are about 12 different loan amount made on different dates.
Lynn

Hi, was my post 11/30/2004 04:44PM GMT no good? What that does is when a payment is made then it recaculates the loan amount and adds on the interest to show a new outstanding figure. Should you want to work out the value to date with no repayment then just put in the date in column A and 0 in coloumn b and copy down the formula.

When I try using your second formula Excel says "#NAME?" in the cell.  I have tried the date as an actual date and a numeric value but it shows the same error.
Lynn

Ack, you may well not have the analysis toolpack installed.

Do:

Tools -> Add-ins...
Tick the box for "Analysis toolpack"
Click Ok.

Goto the cell with the formula in it
Press F2
Press Enter

That worked but I haven't been able to fully test it with the other loans, dates, etc.  I'll let you know.
Thanks
Lynn

I tested the formula using the first loan and the result was $459302.34.  Does that seem rather high?  Then what do I do in column C for the formula to calculate the current amount (principal plus interest).  Does it automatically do that if I keep the same principal amount in Column C?  I think I'm getting rather confused.  Plus I wasn't sure what you meant by "Cell A2 - Ax, etc.

Hi Geoff,

I hadn't picked up that the loan payments were irregular

Cheers

Dave

Lynn,

Understood that the loan repayments will be irregular. Now one more thing.
When a repayment is made, is there a rule about how much of it is considered
as interest and how much is adjusted against the principal? This is relevant
for the calculation.

- sto

>is there a rule about how much of it is considered as interest and how much is adjusted against the principal?

Actually sto, it isn't.

The repyament pays off part of an accumalated debt at that point in time, the debt is made up of both principal and interest. So when you make a pament it reduces the total debt, it isn't apportioned against interest and principal

You may be thinking about a typical home loan where typically the rate (PMT) is constant over the life of the loan. ALthough people often split PMT into interest (IPMT) and principal (PPMT) components it isn't true to say that a payment pays part interest and part principal. It simply reduces the total debt.

Cheers

Dave

Dave,

you are absolutely right if reducing balance basis applies (i.e. interest is computed
on the reduced balance after every repayment). But not otherwise like in the case
of a typical home loan where there may be "annual rests". In the case of annual rests,
principal amount reduces only once a year, and so the effective interest is actually
higher than the interest rate they claim.

When I asked Lynn that question, I was not attempting to fill a vacuum in my understanding
of the principles of loans and interests (hopefully I don't have too much of a vacuum).
I was merely intending to ask what is the specification of Lynn's problem.

Thanks, all the same, for clarifying because I may be adding an unwanted complication.

Over to Lynn!

- sto

Hi Lynn,

>I tested the formula using the first loan and the result was $459302.34.  Does that seem rather
>high?  Then what do I do in column C for the formula to calculate the current amount
>(principal plus interest).  Does it automatically do that if I keep the same principal amount in
>Column C?  I think I'm getting rather confused.  Plus I wasn't sure what you meant by "Cell
>A2 - Ax, etc.

OK, I'll walk you through it :)

In cell A1 put the start date of the loan. In this example we are using 04/30/03, so put that in exactly like that.

In cell B1 we put the interest rate of the loan. In this case 35%. So just put 35% (including the percent sign!) in cell B1

In cell C1 we put the starting amount of the loan. Which in this case is 12400


Right, now we have our base values set up then we can start putting in repayments. The whole A2-Ax thing just means that you can use lines A2 to A whatever.

OK, so lets say that the first repayment is made on the 05/28/03 and 400 is repayed.

So, in cell A2 put the date of repayment - 05/28/03, and in cell B2 put the amount of repayment - 400.
Now, to work out the balance left after that repayment you need to formula in cell C2:
=C1-CUMIPMT($B$1/365,INT(A1-B1),C1,1,INT(A1-B1),1)-B2

When you have put that formula into C2 and pressed enter then the amount that comes up is the remaining value of the loan, with the interest for 04/30/03 to 05/28/03 already included.

OK, another repayment is made - 06/17/03 and the amount 500

Cell A3 put 06/17/03 and cell B3 put 500. Now copy the formula in cell C2 into cell C3 (highlight C2 -> Edit -> Copy -> Highlight C3 -> Edit -> Paste. Or you can fill down).

Now C3 will again have the remaining value of the loan, with the interest for 05/28/03 to 06/17/03 included.

You can keep on adding repayments down the page in columns A and B, and copying down the formula in C as many times as you like.

OK, so lets say we have several payments already made and now the customer wants to know the current value of the loan, with all the interest up to date included:
Put the current day into column Ax (where x is the next new row) and 0 in column Bx. Now copy down the formula in C and you will have a value for balance including all the interest up to date.

Hope this helps some.

Geoff.

Geoff,
Thanks for the step by step.  I can't imagine my boss wants it to be at that high of an interest rate though since the current loan amount as of 12/2/04 for the $12,400 with one payment of $1400 is over $16 million.  It seems the interest rate should be lower to make the payback more reasonable, don't you.  Or the formula adjusted or am I still doing something wrong.
Lynn

What a difference that made!!!  So much more realistic.  Thanks so much.  I do have 2 more questions.  Can I make the "date" column automatically insert the current date so everytime I open the worksheet it will have the current calculation?  I know to copy the formula down in the other column.  I know we have done this on letter templates so I assume it can be done on an Excel worksheet also.  The 2nd question is would it be better to have a separate worksheet for each loan or can I combine them?

First of all I would use a separate sheet for each loan. You can have them all in one workbook, but I wouldn't suggest having them on one sheet.

You can have it insert the current date. You need to put this code in the ThisWorkbook code module of VBA.

Private Sub Workbook_Open

Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook
sh.Range("B65000").End(xlUp).Offset(1,-1).Value = Now
sh.Range("B65000").End(xlUp).Offset(1,-1).NumberFormat = "mm/dd/yyyy"
sh.Range("B65000").End(xlUp).Offset(1,1).Formula = sh.Range("B65000").End(xlUp).Offset(0,1).Formula
Next
Sheets(1).Activate
Application.ScreenUpdating = True
End Sub

I thought using a separate sheet for each loan would be best and then have a recap sheet for the total amounts.  I'm sorry but I don't understand what you are talking about in reference to inserting the current date.  Maybe I'm making it more complicated than is needed.
Lynn

Nice job Geoff.

This was one of the more interesting theads I've seen here.

Cheers

Dave

Lynn,

OK, if, when in Excel, you press Alt+F11, the VBA window will come up. At the left hand side of the screen you will see something called the project tree (looks a bit like windows explorer.) In that tree there will be a main branch called "VBA Project (xxxxx.xls)" in bold where xxxxx is the name of your workbook. If you expand this branch you will see a branch called "Microsoft Excel Objects" If you expand this branch you will see a icon for each of your sheets and also one called "ThisWorkbook". Double click on this and a window will open. Now if you copy and paste the code into there then whenever you open the workbook it will add a 'dummy' record to each sheet which will basically show you the value of each loan up to date.

Actually, since you are going to have a recap sheet then I need to change the code a little. Paste in this code. Make sure you call your recap sheet "Recap" (no double quotes)

Private Sub Workbook_Open()

Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook
If sh.Name <> "Recap" Then
sh.Range("B65000").End(xlUp).Offset(1,-1).Value = Now
sh.Range("B65000").End(xlUp).Offset(1,-1).NumberFormat = "mm/dd/yyyy"
sh.Range("B65000").End(xlUp).Offset(1,1).Formula = sh.Range("B65000").End(xlUp).Offset(0,1).Formula
End If
Next
Sheets(1).Activate
Application.ScreenUpdating = True
End Sub

Oh, and thanks for the grade! :)

I found where to insert this code but when I open the sheet this is what I get:  "Run Time Error 438, Object deosn't support this property or method" and then it flags "For Each sh In This Workbook".  Can you help.

Lynn

Try changing that line to

For Each sh In ThisWorkbook.Sheets

MUK.

Thanks that seemed to work for the date. Thanks.  I hope this is all the help I need on this.
Lynn

no problem, glad we got there in the end.

Have a good weekend!

It's me again.  The formula isn't working anymore.  I have even tried putting it in a brand new workbook as if I was starting from scratch and it just comes up with #NUM! or #NAME?.  I've even taken out the VBA code to see if just adding the dates manually would work, but nothing.  I've even copied the part of the forumula that worked to a new workbook but nothing.  I don't know what to do, can you help.
Lynn

Have you still got the Analysis Toolpack installed? Sounds like this might be the problem.

Tools -> Addins -> Make sure Analysis Toolpack is ticked. If it is try unchecking it, OK, the going back and checking it again.

Thank you, that worked.  I wouldn't have thought that if the Toolpack was already checked it would have to be unchecked and checked again.  I just hope I won't have to do that everytime I open the file, but at least I know how to fix it.
Lynn

OK, you can put this code in the ThisWorkbook code window in the VBA editor. It will make sure the toolpack is installed and if not install it:

Private Sub Workbook_Open()
Dim AddI As AddIn
On Error Resume Next
Application.AddIns.Add "C:\Program Files\Microsoft Office\Office\LIBRARY\ANALYSIS\ANALYS32.XLL", True
Application.AddIns("Analysis Toolpak").Installed = True
On Error GoTo 0
For Each AddI In Application.AddIns
    If AddI.Installed = True Then
        AddI.Installed = False
        On Error Resume Next
        Application.AddIns.Add AddI.Path & "\" & AddI.Name, True
        On Error GoTo 0
        AddI.Installed = True
    End If
Next
End Sub

I opened the worksheet today and it went back to not calculating again.  Do I put the above code right underneath the existing code? I tried that but got errors.  Where should I put it?

OK, the two codes need to be combined into one:

Private Sub Workbook_Open()

Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook
If sh.Name <> "Recap" Then
sh.Range("B65000").End(xlUp).Offset(1,-1).Value = Now
sh.Range("B65000").End(xlUp).Offset(1,-1).NumberFormat = "mm/dd/yyyy"
sh.Range("B65000").End(xlUp).Offset(1,1).Formula = sh.Range("B65000").End(xlUp).Offset(0,1).Formula
End If
Next
Sheets(1).Activate
Application.ScreenUpdating = True

Dim AddI As AddIn
On Error Resume Next
Application.AddIns.Add "C:\Program Files\Microsoft Office\Office\LIBRARY\ANALYSIS\ANALYS32.XLL", True
Application.AddIns("Analysis Toolpak").Installed = True
On Error GoTo 0
For Each AddI In Application.AddIns
    If AddI.Installed = True Then
        AddI.Installed = False
        On Error Resume Next
        Application.AddIns.Add AddI.Path & "\" & AddI.Name, True
        On Error GoTo 0
        AddI.Installed = True
    End If
Next

End Sub

If you still get errors with this, click debug and tell me which line is highlighted.

Cheers,
Geoff.

The part that was hightlighted was the line where we had to add .sheets.  I added that, didn't get the debug error but the formula still doesn't work.

OK, sorry, wrong way round for mixing the codes up:

Private Sub Workbook_Open()

Dim AddI As AddIn
On Error Resume Next
Application.AddIns.Add "C:\Program Files\Microsoft Office\Office\LIBRARY\ANALYSIS\ANALYS32.XLL", True
Application.AddIns("Analysis Toolpak").Installed = True
On Error GoTo 0
For Each AddI In Application.AddIns
    If AddI.Installed = True Then
        AddI.Installed = False
        On Error Resume Next
        Application.AddIns.Add AddI.Path & "\" & AddI.Name, True
        On Error GoTo 0
        AddI.Installed = True
    End If
Next

Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Recap" Then
sh.Range("B65000").End(xlUp).Offset(1,-1).Value = Now
sh.Range("B65000").End(xlUp).Offset(1,-1).NumberFormat = "mm/dd/yyyy"
sh.Range("B65000").End(xlUp).Offset(1,1).Formula = sh.Range("B65000").End(xlUp).Offset(0,1).Formula
End If
Next
Sheets(1).Activate
Application.ScreenUpdating = True

Calculate

End Sub

Hopefully this will work!

Sorry, but I'm still getting the #NUM! error even after putting in the new VBA formula.  This is sure getting frustrating.  Any other ideas?

Hmm, ythe NUM error only occurs when the start date is greater than the end date, or the start date is less than 0. Are you sure that you are a) not puttting dates in the future in and b) using a correct date in the date column?

I am not putting in any future dates.  When I open up the document the current date is automatically added (which is what we wanted), and the NUM error occurs on the first worksheet in column C.  If I got to the other worksheets, the current date is there with the same number in column C as the one above it.  There isn't a NUM error but the amount is the same as from the previous date.  I wish you could actually see what I'm doing.

OK, I think I MAY have the problem. Change this line in the code:

sh.Range("B65000").End(xlUp).Offset(1,1).Formula = sh.Range("B65000").End(xlUp).Offset(0,1).Formula

TO:

sh.Range("B65000").End(xlUp).Offset(1,1).FormulaR1C1 = sh.Range("B65000").End(xlUp).Offset(0,1).FormulaR1C1

I hope!

It didn't change anything.  I'm still getting the NUM error.

Right, we'll try actually setting the formula:

Private Sub Workbook_Open()

Dim AddI As AddIn
On Error Resume Next
Application.AddIns.Add "C:\Program Files\Microsoft Office\Office\LIBRARY\ANALYSIS\ANALYS32.XLL", True
Application.AddIns("Analysis Toolpak").Installed = True
On Error GoTo 0
For Each AddI In Application.AddIns
    If AddI.Installed = True Then
        AddI.Installed = False
        On Error Resume Next
        Application.AddIns.Add AddI.Path & "\" & AddI.Name, True
        On Error GoTo 0
        AddI.Installed = True
    End If
Next

For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Recap" Then
With sh.Range("B65000").End(xlUp)
.Offset(1,-1).Value = Now
.Offset(1,-1).NumberFormat = "mm/dd/yyyy"
.Offset(1,1).Formula = "C" & .Offset(0, 1).Row & "-CUMIPMT($B$1/365,INT(A" & .Offset(1, -1).Row & "-A" & .Offset(0, -1).Row & "),C" & .Offset(0, 1).Row) & ",1,INT(A" & .Offset(1, -1).Row & "-A" & .Offset(0, -1).Row & "),1)-B" & .Offset(1, 0).Row
End With
End If
Next
Sheets(1).Activate
Application.ScreenUpdating = True

Calculate

End Sub


I have tested this and it seems to work fine.

MUK.

When I opened it today some of the worksheets were OK but some weren't.  So I deleted the rows from yesterday and they seem to have calculated correctly.  I'll see what happens tomorrow or next week.  Thanks again for your help.

ok, np.