# Interest calculation formula

#### jesstercpa

##### New Member
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.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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

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%)))

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

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

Replies
9
Views
258
Replies
1
Views
466
Replies
7
Views
301
Replies
2
Views
244
Replies
2
Views
202

1,218,575
Messages
6,143,312
Members
450,477
Latest member
teresab543

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back