# SUMPRODUCT formula & chart for blended tier pricing

#### tammyanthos

##### New Member
Blended tier pricing

I need to create a chart and formula that calculates what the price = based on the \$ amount of the account

e.g. accounts up to \$100k = .50, \$100k-\$250k = .60, etc. so an account of \$175k would be.50 for the first \$100k and .60 for the other \$75k ?

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### COwen

##### Board Regular
I do not fully understand your requirements. Here is a formula that will find the price up to \$250k...you did not provide rates after that.
Code:
``=TEXT(IF(J19>100000,IF(J19<=250000,100000*0.5+((J19-100000)*0.6),TEXT(J19,"\$#,000.00")&" Is larger than \$250k"),J19*0.5),"\$#,000.00")``
I do not know what you need. please clarify your requirements for better help.

#### tammyanthos

##### New Member
I do not fully understand your requirements. Here is a formula that will find the price up to \$250k...you did not provide rates after that.
Code:
``=TEXT(IF(J19>100000,IF(J19<=250000,100000*0.5+((J19-100000)*0.6),TEXT(J19,"\$#,000.00")&" Is larger than \$250k"),J19*0.5),"\$#,000.00")``
I do not know what you need. please clarify your requirements for better help.
https://www.schwabpt.com/public/file/P-9043995/spt010771.pdf

this is an example of what I need to build and wanted to use SUMPRODUCT formula to do so

#### COwen

##### Board Regular
I am not sure how to get a SUMPRODUCT to break apart the values if they are above and below the criteria. My formula does what you want it to do. If you give me more rates, (e.g. >\$250k) then I will be happy to build a full formula.

#### tammyanthos

##### New Member
I am not sure how to get a SUMPRODUCT to break apart the values if they are above and below the criteria. My formula does what you want it to do. If you give me more rates, (e.g. >\$250k) then I will be happy to build a full formula.
Thank you!

I would want to be able to plug in a value of X (e.g. \$175,000) in somewhere on the excel sheet and have it determine the total % we should charge

\$0-\$150,000 = .5%
\$150,000-\$250,000 = .6%
\$250,000-\$500,000 = .7%
\$500,000-\$1,000,000 = .8%
Greater than \$1,000,000 = 9%%

#### COwen

##### Board Regular
Do you mean 9% for >1,000,000? 9%% is 0.0009...

Also, do you want it to multiply the numbers accordingly or just give you percentages?

#### tammyanthos

##### New Member
Do you mean 9% for >1,000,000? 9%% is 0.0009...

Also, do you want it to multiply the numbers accordingly or just give you percentages?
9% sorry

#### tammyanthos

##### New Member
can it do both ? give me the total \$ and what the % comes out to? for example maybe an account value of \$625,000 = .725% or \$4531.25