Interest calculation formula

jesstercpa

New Member
Joined
Mar 1, 2002
Messages
4
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!
 

Excel Facts

Who is Mr Spreadsheet?
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
 
Upvote 0
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%)))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top