Interest calculation formula

[jesstercpa]

Good morning.

I need a formula figured out and I just keep running into a wall with this one. Your help is greatly appreciated.

I have to calculate interest on a tax lien and subsequent tax payments. The first $1500 gets charged 8%, the rest get 18%. I have in column H the total lien. Then in column U I have the subsequent payments. What keeps baffling me is how to do this if the lien is less than $1500, then the subsequent payments bring the total over $1500. I hope I have not confused you all.

Thanks again for your help!

 

[plettieri]

Good morning,

If you have this on work sheet already care to post a short version of yr data and the expected out you would like to see...

You can use the "Download Colo's cool utility for displaying your Excel Worksheet on the board" for ease

pll

 

[DonkeyOte]

are you saying that these are accumulative payments and that up to 1500 you get @ 8% and then the remainder gets 18%?

Let's say

H1 = total = 850
U1 = sub payment = 1000

Then tax...

=IF(H1>=1500,U1*18%,IF(H1+U1<1500,U1*8%,((1500-H1)*8%)+(((H1+U1)-1500)*18%)))

 

[Dave Patton]

consider

=MIN(H1+U1,1500)*0.08+MAX((H1+U1-1500),0)*0.18

or
=IF(H1+U1<=1500,(H1+U1)*0.08,1500*0.08+(H1+U1-1500)*0.18)
This message was edited by dave patton on 2002-10-14 12:39

 

[plettieri]

To add a few wrinkles....

When I use the suggested help replies so far on a variety of possible inputs, I get three different answers.....What is the outcome you want to see if H1 = 1500 and U1 is 1200? Can the answer ever be negative?


pll