Question

Excel Formula to calculate interest based on dates

Asked by: hbtitansfan

I need an Excel formula that calculates interest on several different loan amounts on a daily basis ongoing.  For example, one loan is in the amount of $12,400 and occurred 4/30/03.  It is to be paid back at 35% interest rate.  Another loan is for $100,000 occurring on 9/1/0/03 to be paid back at an interest rate of 12%.  I need to know on any given day based on when the loan originated (date is a factor) how much is due.  There are about 12 different loan amounts.  I also have to show the balance when a protion of each loan is paid back.  I hope I've explained this correctly.  

Thank you,
Lynn

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2004-11-30 at 07:49:48ID21224795
Tags

formula

,

excel

,

calculate

,

interest

Topic

Microsoft Excel Spreadsheet Software

Participating Experts
4
Points
500
Comments
45

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Loan Payments
    Am looking for the formulas to calculate the monthly payment given the interest, num of years, and loan amount. Am needing the formula for calculating the payment and then to figure the principal and interest portion over the life of the loan (amortization). Using VB5 and ...
  2. loan amortization....
    loan amortization, is there a way to do this in exel? If not points to whoever can tell me how (besided buying quicken)
  3. Calculating Yield on a Set of Loans
    I need to calculate a yield on a set of loans (different amounts and interest rates) for quarters and fiscal year. I can't any code for calculating Yield at all? Frank
  4. Find Loan Balance at any Determination Date (factoring for…
    Hello, I am looking for a formula to determine the remaining loan balance for an existing loan at any given determination period. I have been able to find numerous formulas that can show this if the interest calc is done monthly but unfortunately i need something that handl...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: MalicUKPosted on 2004-11-30 at 07:56:22ID: 12707037

Hi Lynn,

I'm not the greatest at finantial functions but you might want to try some of the functions in Excel:

CUMIPMT
IPMT

Also you can do a search on Excel Help for "interest rate worksheet functions"

Hope this helps. I'll have a play with these now to see if I can come up with anything.

Cheers,
MalicUK.

 

by: MalicUKPosted on 2004-11-30 at 08:16:06ID: 12707303

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!

 

by: MalicUKPosted on 2004-11-30 at 08:44:12ID: 12707648

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.

 

by: brettdjPosted on 2004-11-30 at 13:58:06ID: 12710721

> 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

 

by: stochasticPosted on 2004-11-30 at 22:43:12ID: 12713378

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

 

by: hbtitansfanPosted on 2004-12-01 at 08:26:17ID: 12717489

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

 

by: MalicUKPosted on 2004-12-01 at 08:29:42ID: 12717550

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.

 

by: hbtitansfanPosted on 2004-12-01 at 09:10:50ID: 12718170

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

 

by: MalicUKPosted on 2004-12-01 at 09:14:10ID: 12718221

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

 

by: hbtitansfanPosted on 2004-12-01 at 09:45:22ID: 12718519

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

 

by: hbtitansfanPosted on 2004-12-01 at 13:28:36ID: 12720700

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.

 

by: brettdjPosted on 2004-12-01 at 15:05:34ID: 12721584

Hi Geoff,

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

Cheers

Dave

 

by: stochasticPosted on 2004-12-01 at 22:00:14ID: 12723403

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

 

by: brettdjPosted on 2004-12-01 at 22:08:45ID: 12723440

>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

 

by: stochasticPosted on 2004-12-02 at 00:32:18ID: 12723865

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

 

by: MalicUKPosted on 2004-12-02 at 01:14:33ID: 12724141

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.

 

by: hbtitansfanPosted on 2004-12-02 at 07:27:25ID: 12726573

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

 

by: MalicUKPosted on 2004-12-02 at 07:45:57ID: 12726776

OOOOPPPSSS!!

Knew I'm find a mess up there. I wrote the function wrong. Try this amend, it comes out a bit more realistic :)

=C1-CUMIPMT($B$1/365,INT(A2-A1),C1,1,INT(A2-A1),1)-B2

 

by: hbtitansfanPosted on 2004-12-02 at 08:38:05ID: 12727389

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?

 

by: MalicUKPosted on 2004-12-02 at 08:43:26ID: 12727449

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

 

by: hbtitansfanPosted on 2004-12-02 at 11:41:59ID: 12729486

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

 

by: brettdjPosted on 2004-12-02 at 16:16:46ID: 12731760

Nice job Geoff.

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

Cheers

Dave

 

by: MalicUKPosted on 2004-12-03 at 01:15:54ID: 12734048

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

 

by: MalicUKPosted on 2004-12-03 at 01:16:42ID: 12734054

Oh, and thanks for the grade! :)

 

by: hbtitansfanPosted on 2004-12-03 at 06:28:49ID: 12736182

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

 

by: MalicUKPosted on 2004-12-03 at 08:51:27ID: 12737727

Try changing that line to

For Each sh In ThisWorkbook.Sheets

MUK.

 

by: hbtitansfanPosted on 2004-12-03 at 09:01:41ID: 12737853

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

 

by: MalicUKPosted on 2004-12-03 at 09:06:09ID: 12737911

no problem, glad we got there in the end.

Have a good weekend!

 

by: hbtitansfanPosted on 2004-12-10 at 11:00:58ID: 12795241

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

 

by: MalicUKPosted on 2004-12-13 at 01:26:29ID: 12807319

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.

 

by: hbtitansfanPosted on 2004-12-13 at 08:33:44ID: 12810698

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

 

by: MalicUKPosted on 2004-12-14 at 04:05:31ID: 12818183

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

 

by: hbtitansfanPosted on 2004-12-14 at 07:58:09ID: 12820565

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?

 

by: MalicUKPosted on 2004-12-14 at 08:00:22ID: 12820603

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.

 

by: hbtitansfanPosted on 2004-12-14 at 08:31:53ID: 12821050

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.

 

by: MalicUKPosted on 2004-12-14 at 08:40:09ID: 12821157

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!

 

by: hbtitansfanPosted on 2004-12-15 at 07:17:25ID: 12830669

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?

 

by: MalicUKPosted on 2004-12-15 at 07:37:42ID: 12830949

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?

 

by: hbtitansfanPosted on 2004-12-15 at 08:26:54ID: 12831544

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.

 

by: MalicUKPosted on 2004-12-15 at 08:30:55ID: 12831595

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!

 

by: hbtitansfanPosted on 2004-12-15 at 09:24:56ID: 12832322

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

 

by: MalicUKPosted on 2004-12-16 at 01:11:53ID: 12838473

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.

 

by: hbtitansfanPosted on 2004-12-16 at 08:14:07ID: 12842135

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.

 

by: MalicUKPosted on 2004-12-16 at 08:20:31ID: 12842233

ok, np.

 

by: katdunnPosted on 2009-02-12 at 13:22:21ID: 23626968

Excel

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...