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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Well-known Member
Joined
Feb 15, 2002
Messages
4,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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
 

Forum statistics

Threads
1,144,116
Messages
5,722,568
Members
422,447
Latest member
srclife

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
Top