Calculate total cost based off time left with different yearly rate

[bregier79]

Perhaps I'm getting rusty at Excel since I've been doing many of my recent analysis in Spotfire, but I need some help! I'm trying to develop a formula that will provide total cost based off how many months left in a contract with a different monthly rate each year. So for example in 2021 the rate is $22 then in 2022 it is $25, and so on - I want to take that and factor in the cost based on the current date and term remaining (rounding up please). Any ideas?

 

[The_Macrotect]

Hey there,

I believe I'm at least close to solving this one...

I inserted some helper columns to calculate the number of months to be charged for in each year, and put in a formula for today's date at the bottom left. Does this look like what you need?

Excel 2007 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Contract Expiration​	2021 Rate (per month)​	2022 Rate (per month)​	2023 Rate (per month)​	Months Remaining​	2021 Months​	2022 Months​	2023 Months​	Total Commitment​
2​	4/12/2022​	20​	23​	25​	10​	=IF(TEXT($A$13,"mm")+E2>=12,12-TEXT($A$13,"mm"),IF(E2+TEXT(A13,"mm")<12,E2,E2-TEXT($A$13,"mm")+2))​	=IF(E2-F2=0,0,IF(E2-F2>12,12,E2-F2))​	=IF(E2-F2-G2=0,0,E2-F2-G2)​	=F2*B2+G2*C2+H2*D2​
3​	4/19/2022​	15​	17​	22​	10​	=IF(TEXT($A$13,"mm")+E3>=12,12-TEXT($A$13,"mm"),IF(E3+TEXT(A14,"mm")<12,E3,E3-TEXT($A$13,"mm")+2))​	=IF(E3-F3=0,0,IF(E3-F3>12,12,E3-F3))​	=IF(E3-F3-G3=0,0,E3-F3-G3)​	=F3*B3+G3*C3+H3*D3​
4​	5/1/2021​	5​	8​	12​	-3​	=IF(TEXT($A$13,"mm")+E4>=12,12-TEXT($A$13,"mm"),IF(E4+TEXT(A15,"mm")<12,E4,E4-TEXT($A$13,"mm")+2))​	=IF(E4-F4=0,0,IF(E4-F4>12,12,E4-F4))​	=IF(E4-F4-G4=0,0,E4-F4-G4)​	=F4*B4+G4*C4+H4*D4​
5​	3/8/2022​	7​	14​	18​	9​	=IF(TEXT($A$13,"mm")+E5>=12,12-TEXT($A$13,"mm"),IF(E5+TEXT(A16,"mm")<12,E5,E5-TEXT($A$13,"mm")+2))​	=IF(E5-F5=0,0,IF(E5-F5>12,12,E5-F5))​	=IF(E5-F5-G5=0,0,E5-F5-G5)​	=F5*B5+G5*C5+H5*D5​
6​
7​
8​
9​
10​
11​
12​
13​	=TODAY()​

Sheet: Sheet3