Excel Help

[Fresh Tendrils]

Okay, I've been tasked to try and create an interest calculator. The catch is that the interest rates change for each quarter, so I need to do a formula that will take the start and end dates and include all the interest rates (each with their specific number of days) in that range.

I'm not really sure how to write a formula that will include and calculate multiple variables. I can do a simple lookup, but that only gets me the interest rates on the start date and on the end date, but not between.

Any suggestions?

[wwharton]

Re: Excel Help

Question's not completely clear. If you have a number of interest rates (and that number isn't going to change) then you can select all the cells while creating your formula. If the range isn't static then you'll either have to modify your formula each time or do something a bit more complicated.

Trying to paint a picture of what you want to do, I'm not sure if you want to use Access for the bulk of it, and have a report spit out to Excel (may not even need to do that part).

[Fresh Tendrils]

Re: Excel Help

I want to calculate total payment (original payment due + accumulated interest) for a late IRS tax payment.

To calculate there are these inputs: a date due, date paid, and original payment amount.

Based on the due date and the date paid, I want to find the applicable interest rates (the interest rates will not only include the interest rates at both dates, but those in between the dates too) and use them in a formula to calculate the accumulated interest.

I have a table set up of the interest rates and the date range that each interest rate is set through. I had in mind of doing a vlookup, but I'm not sure how to do a "between" function in pre-07 Excel.

It's easy to do "by hand", but I was hoping I could create an excel spreadsheet that would be faster.

[wwharton]

Re: Excel Help

Still trying to picture it but it sounds like you have at least 2 interest rates with X rates to also be considered? Since it's a variable number, you'll have to use a loop which is why I was thinking making in a database (Access) would make more sense... but still not sure that's necessary. If you have any experience with Access, you may want to give it a shot though bc it'd probably be easier. General interest information is kept in one table while client info is in another. It'd then spit out a report (xls) of the cross between necessary data based on if/then statements.

Doesn't sound like you're doing advanced calculations once you nail this part down so you may even be able to do the whole thing in Access... but even if you are, spit out the spreadsheet and then take care of the rest.

Excel can be used as a database and can do what you're looking for, but I'm not a power user to that extent bc my mind thinks db for "many to one" situations like this... even though calculations are involved.

[Fresh Tendrils]

Re: Excel Help

I'm probably making it harder than it is.

I don't have Access at work. My boss just wanted to see if this could be done rather than using some other program, so I feel compelled to find a way, haha.

Anyway, basically my data is a table on a separate tab. The columns are: start date, end date, number of days, and interest rate.

The inputs will be: due date, date paid, and original amount due.

Basically, I want to use the inputs to look at the table, match up the due date and date paid to a range in the table and include any interest rate in that range for my computation.

For example, here's a table:


1/1/2006 4/30/2006 # of days 8%
5/1/2006 7/31/2006 # of days 7%
....
...
...
1/1/2009 3/31/2009 # of days 6%


Let's say the original date due is April 15th, 2006, but you don't pay it until February 19th 2009. I would like for it to return the interest rate as of April 15th through February 19th. I can only manage to come up with returning the interest rate for April 15th and February 19th, but not the interest rates in between those dates.


I hope that makes sense. The calculation itself is fairly simple, but for some reason coming up with an excel formula is just not clicking.

[wwharton]

Re: Excel Help

Sorry man, haven't been around much for a couple of days. Are you still working on this? Do you have a blank template (or one you can fill with dummy data) with your current process that you may be able to send me?

I know you can do this. I think it's just difficult figure it out trying to paint the picture.

[Fresh Tendrils]

Re: Excel Help

I think I can get it with nested IF statements, but I'm still ironing it out. I probably won't be able to get time to send it out today, but if I haven't honed in on a solution I'll hit ya up on PM next week.

[wwharton]

Re: Excel Help

Cool. If so, I'll see what I can do (or bring in a coworker that does more advanced stats in excel... been a while since I've gone that deep).