Need formula help to create a tiered interest loan worksheet!

Hi,

I'm working on creating a worksheet that calculates a tiered interest rate on a one year loan and could use some help. I've attached a worksheet - I'm not sure how to build a formula that recognizes and applies the correct interest rate for each tier of the balance.

mana2012-Example-01.xlsx

I can work this out manually on paper but it's taking forever. I've done some reading and it seems I'll need to use multiple logic tests and possibly the sumproduct formula as well. Would anyone be willing to give me a little assistance? I'd truly appreciate it

Thanks!!

Jayson

Alan,

First of all, thank you for the help!!! We're defiantly on the right track!

I've copied the formula you wrote to the worksheet and it's not returning any errors but the interest due it's calculating is much lower than what I calculated manually (see attached worksheet). I've revised my example spreadsheet with the *old* manual formula I'm currently using to calculate and verify customers loan payments. Can you take another look at these and see were the adjustment needs to be made?

I truly appreciate your time!

-Jayson

mana2012-Example-02.xlsx

Mana2012-Manual-Work.pdf

Alan,

Yes, these are monthly rates. And you're correct, I'm developing the sheet to verify title loans based on VA title lending rates (very similar to payday). I've tweaked the formula a little in the past few min:

=IF(D13<=700,((D13*2.64)*C13/365),IF(AND(D13>700,D13<=1400),((700*2.64)*C13/365)+(((D13-700)*2.16)*C13/365),(((700*2.64)*C13/365)+(((D13-700)*2.16)*C13/365)+(((D13-1400)*1.8)*C13/365))))

I'm getting a result of ~ $511 which is a little high. Could the problem be with the final logic test's "value if false"? I'm thinking D13-700 could possibly produce an amount over $700 and allow some of the balance to be charged interest twice.

Really appreciate the help!

-Jayson

Ok, I've tested my theory a few more time and I think I've found the problem - It looks like the "D13-700" will result in a value greater than $700 if the balance is over $1400, therefore allowing interest to be charged twice on a portion of the balance. How can I adjust the formula to limit that tier to the portion of the balance that's greater than $700 (if any) but nothing more than $1400 (if any). Anything over $1400 is being caculated correctly by the later part of the formula.

=IF(D13<=700,((D13*2.64)*C13/365),IF(AND(D13>700,D13<=1400),((700*2.64)*C13/365)+(((D13-700)*2.16)*C13/365),(((700*2.64)*C13/365)+(((D13-700)*2.16)*C13/365)+(((D13-1400)*1.8)*C13/365))))

I'm thinking I'll need another logic test... something like IF(AND.... but I can't seem to get it right on my own.

Any help would be appreciated!

-Jayson

Yes, I did that and found the results were correct. However, if the balance is over $700, but under $1400 the number in that cell will not be 700. I've played with it more and came up with the following:

=IF(D16<=700,((D16*2.64)*C16/365),IF(AND(D16>700,D16<=1400),((700*2.64)*C16/365)+(((D16-700)*2.16)*C16/365),(((700*2.64)*C16/365)+(((MIN(D16-700,700)*2.16)*C16/365)+(((D16-1400)*1.8)*C16/365)))))

So far, it's working. Do you see any shortcomings to my formula for that part?

Thanks for the help!

-Jayson

Yes, I've tested it a few more times and it seems to be working for different loan amounts. Thanks for the help, Alan. Couldn't have done this without your formula!

Take care,
Jayson