SUMPRODUCT formula & chart for blended tier pricing

tammyanthos

New Member
Joined
Apr 6, 2020
Messages
36
Office Version
365
Platform
Windows
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 ?
 

Some videos you may like

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
Joined
Feb 27, 2020
Messages
221
Office Version
365, 2019, 2016
Platform
Windows
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
Joined
Apr 6, 2020
Messages
36
Office Version
365
Platform
Windows
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
Joined
Feb 27, 2020
Messages
221
Office Version
365, 2019, 2016
Platform
Windows
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
Joined
Apr 6, 2020
Messages
36
Office Version
365
Platform
Windows
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
Joined
Feb 27, 2020
Messages
221
Office Version
365, 2019, 2016
Platform
Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,267
Messages
5,443,434
Members
405,235
Latest member
1Thess521

This Week's Hot Topics

Top