Looking up interest rate to create blended rate

[Paul_dont_know]

I have used Excel for many years and I don't know why I can't figure this out. I am needing to determine an interest rate for payment given the historical bank rate (shown columns E and F). If the bank changed the interest rate at some point between payment dates then an appropriate blended rate would be calculated. In the image shown, I am looking for the formulas to put in B3..B14. It should calculate out to approx the values that are shown in column C (which I calculated by hand so that I could verify if my B column formulas are correct). The data here is simplified for easy of use. The actual date/data ranges are much longer. Hence not wanting to do it all by hand.

The blended rate is just the rate before the bank rate hike, prorated for the number of days before the rate hike + the rate after the rate hike prorated for the number of days after the hike.
I can't figure out how to look up the values and blend the rates.

Any help would be appreciated.

 

[StephenCrump]

Welcome to the Forum!

Try this:

	A	B	C	D	E
1	Payment			History
2	Dec-01-2021			Mar-30-2020	2.45%
3	Jan-01-2022	2.45%		Mar-03-2022	2.70%
4	Feb-01-2022	2.45%		Apr-14-2022	3.20%
5	Mar-01-2022	2.45%		Jun-02-2022	3.70%
6	Apr-01-2022	2.68%		Jul-14-2022	4.70%
7	May-01-2022	2.98%		Sep-08-2022	5.45%
8	Jun-01-2022	3.20%		Oct-27-2022	5.95%
9	Jul-01-2022	3.68%
10	Aug-01-2022	4.28%
11	Sep-01-2022	4.70%
12	Oct-01-2022	5.28%
13	Nov-01-2022	5.53%
14	Dec-01-2022	5.95%
15
Sheet4

Cell Formulas
Range	Formula
B3:B14	B3	=(MIN(A3-VLOOKUP(A3,History,1),A3-A2)*VLOOKUP(A3,History,2)+(A3-A2-MIN(A3-VLOOKUP(A3,History,1),A3-A2))*VLOOKUP(A2,History,2))/(A3-A2)
A4:A14	A4	=EDATE(A3,1)

Named Ranges
Name	Refers To	Cells
History	=Sheet4!$D$2:$E$8	B3:B14

Note that this formula allows only for zero or one change of interest rate between successive payments. The formula could be generalised if you needed to allow for more?

 

[Paul_dont_know]

That's awesome Stephen! It works perfectly. Thank you so much.

 

[StephenCrump]

Great! I'm glad we could help.