HELP: Excel Formula using date as basis for interest

[rcomag]

Hello, newbie here. can someone please help on how to work on the formula using dates (year... if possible movable dates) as basis for computing the interest. i made a formula and i cant seem find anyway to have the year be moving, depending on the present year, or if possible to include the months for the computation of interest. thank you.

=IF(O13>=2020,R13*-20%, IF(O13=2019,R13*24%, IF(O13=2018,R13*48%, IF(O13>=1992,R13*72%, IF(O13>=1974, R13*24%, IF(O13<=1973, R13*12%))))))

 

[Toadstool]

Hi rcomag,

I understand your formula and can reproduce:

	L	M	N	O	P	Q	R
13	1080			1996			1500

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula L13 =IF(O13>=2020,R13*-20%, IF(O13=2019,R13*24%, IF(O13=2018,R13*48%, IF(O13>=1992,R13*72%, IF(O13>=1974, R13*24%, IF(O13<=1973, R13*12%)))))) <tbody> </tbody>

<tbody>
</tbody>

My preferred solution would be to use a table of years and percentages then use VLOOKUP

	L	M	N	O	P	Q	R
1	Year	Interest
2	0	12%
3	1973	12%
4	1974	24%
5	1992	72%
6	2018	48%
7	2019	24%
8	2020	-20%
9 to12
13			24.00%	1982			1500

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula N13 =VLOOKUP(O13,$L$2:$M$8,2,TRUE) <tbody> </tbody>

<tbody>
</tbody>

The VLOOKUP says, take the contents of O13 and look in L2 to L8. If you find a match then retrieve from that row the value in the second column of my search (L2 to M8 so value retrieved from column M).
The TRUE says find an approximate match, so the years in column L must be ascending and if it doesn't find an exact match then it returns the year which is highest which is equal or below the search value.

My table must start with a year zero, or the lowest possible year, so I catch those entries in O13

 

[rcomag]

thank you for your help.. tried the solution you gave and it work. can the return be multiplied directly to another cell instead of the interest rate and can present date be incorporated in the formula??? interest rates are increasing monthly at a rate of 2% monthly up to 3 years, 72% or 36 months (3 years). thank you very much.

 

[Toadstool]

rcomag,

"can the return be multiplied directly to another cell instead of the interest rate"
Yes. If I change the format of cell N13 from percentage to a number then
=R13*VLOOKUP(O13,L2:M8,2,TRUE)
will return 360.00, being 24% of 1500


"can present date be incorporated in the formula?"
If you change the VLOOKUP list from year to date then you can also use an input date.

	L	M	N	O	P	Q	R
1	Year	Interest
2	01-Jan-00	12%
3	06-Jun-73	12%
4	03-Mar-74	24%
5	06-Jun-92	72%
6	04-Apr-18	48%
7	06-Jun-19	24%
8	27-Jul-19	30%
9	24-Aug-19	50%
10	01-Jan-20	-20%
11
12
13			360.00	01-Jan-82			1500
14			450.00	28-Jul-19			1500
15			750.00	25-Aug-19			1500

<tbody>
</tbody>

Sheet3 (2)​

Worksheet Formulas Cell Formula N13 =R13*VLOOKUP(O13,$L$2:$M$10,2,TRUE) N14 =R14*VLOOKUP(O14,$L$2:$M$10,2,TRUE) N15 =R15*VLOOKUP(O15,$L$2:$M$10,2,TRUE) <tbody> </tbody>

<tbody>
</tbody>

Here my original row 13 result is unchanged.

The new row 14 is a 28 July 2019 date. One day after the 27 July 2019 increase to 30%.

Row 15 shows that today I'd get the new rate entered yesterday of 50%.

 

[rcomag]

hello again, can i incorporate these table of penalties to the vlookup formula??

	YEAR	amount	interest	penalty
	2002	707.00	0.72	509.04
	2018	174.80	0.48	83.90
	2019	300.00	0.24	72.00

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

TABLE OF PENALTIES
	january	february	march	april	may	june	july	august	september	october	november	december
1973	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%
1974	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%
1992	72%	72%	72%	72%	72%	72%	72%	72%	72%	72%	72%	72%
2017	50%	52%	54%	56%	58%	60%	62%	64%	66%	68%	70%	72%
2018	26%	28%	30%	32%	34%	36%	38%	40%	42%	44%	46%	48%
2019	-10%	-10%	-10%	8%	10%	12%	14%	16%	18%	20%	22%	24%
2020	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%
	if your due is year 2018 and you paid it in june, you get a penalty of 36%.
	if your due is 2017 and you paid it in october, you get a penalty of 68%.
	if your due is 2019 and you paid it in february, you get a discount of 10%.

<colgroup><col span="2"><col><col><col><col span="8"></colgroup><tbody>
</tbody>

thanks!!!1

 

[Toadstool]

Hi rcomag,

It's easier to stick with the original two column VLOOKUP and you can extend those columns down as many rows as you like (as long as the formulae also look down to the maximum row).

You don't say how the paid date is entered so again I'll assume a full date.

You've taken away the pre-1973 row so I'll wrap the formula in an IFERROR and return zero if an earlier date is selected.

It is possible to do it with VLOOKUP but it gets very messy. Your example data doesn't show it but what if they pay June 1993? VLOOKUP returns the 1992 row and you could select the column related to the month but June 1992 might be at a different rate than December 1992, which is the closest earlier date with data.

I'd change to use INDEX and MATCH to select from that array:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	TABLE OF PENALTIES
2		january	february	march	april	may	june	july	august	september	october	november	december
3	1973	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%
4	1974	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%
5	1992	72%	72%	72%	72%	72%	72%	72%	72%	72%	72%	72%	72%
6	2017	50%	52%	54%	56%	58%	60%	62%	64%	66%	68%	70%	72%
7	2018	26%	28%	30%	32%	34%	36%	38%	40%	42%	44%	46%	48%
8	2019	-10%	-10%	-10%	8%	10%	12%	14%	16%	18%	20%	22%	24%
9	2020	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%
10				................	.......	.......	.......
11				Date Paid	Penalty	Amount	Penalty
12				06-Jun-18	36%	1500	540.00
13				01-Oct-17	68%	1500	1020.00
14				02-Feb-19	-10%	1500	-150.00
15				02-Feb-93	72%	1500	1080.00
16				01-Jan-21	-20%	1500	-300.00
17				01-Jan-45	0%	1500	0.00
18				03-Mar-02	72%	707	509.04

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E12 =IFERROR(IF(ISNA(MATCH(YEAR(D12),$A$3:$A$9,0)),INDEX($M$3:$M$9,MATCH(YEAR(D12),$A$3:$A$9,TRUE)), INDEX($B$3:$M$9,MATCH(YEAR(D12),$A$3:$A$9,0),MONTH(D12))),0) G12 =F12*E12 <tbody> </tbody>

<tbody>
</tbody>

 

[rcomag]

hello again, thank you for helping me.. tried your formula but couldn't still figure it out. anyways, here is what i've tried doing. i still can't get the right interest rate.

							August 26, 2019
			10,153.04
		TABLE OF PENALTIES
			`
					interest RATE	correct				january	february	march	april	may	june	july	august	september	october	november	december
		PAYABLE	YEARS	AMOUNT	from formula	interest rate	INTEREST	TOTAL	1973	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%
252,470.00		2019	2019	2,524.70	0.16	16%	403.95	2,928.65	1974	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%
23,640.00		2018	2018	236.40	0.16	40%	94.56	330.96	1992	72%	72%	72%	72%	72%	72%	72%	72%	72%	72%	72%	72%
23,640.00		2017	2017	236.40	0.16	64%	151.30	387.70	2017	50%	52%	54%	56%	58%	60%	62%	64%	66%	68%	70%	72%
23,640.00		2001	2016	3,782.40	0.16	72%	2,723.33	6,505.73	2018	26%	28%	30%	32%	34%	36%	38%	40%	42%	44%	46%	48%
									2019	-10%	-10%	-10%	8%	10%	12%	14%	16%	18%	20%	22%	24%
		TOTAL	6,779.90			3,373.14	10,153.04	2020	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%
FORMULA THAT I USE: =IFERROR(IF(ISNA(MATCH(YEAR($Q$10),$AB$19:$AB$25,0)),INDEX($AB$19:$AN$25,MATCH(YEAR($Q$10),$AB$19:$AB$25,TRUE)),INDEX($AC$19:$AN$25,MATCH(YEAR($Q$10),$AB$19:$AB$25,0),MONTH(TODAY()))),0)

<colgroup><col><col><col><col><col span="3"><col><col><col><col span="12"></colgroup><tbody>
</tbody>

Q10 refers to the date, august 26, 2019

couldn't figure how to use the payable years as basis for the interest aside from as to the date of payment.
thanks.....

<colgroup><col><col><col><col><col span="3"><col><col><col><col span="12"></colgroup><tbody>
</tbody>

 

[Toadstool]

First let me advise you remove the IFERROR while testing as it masks most errors and is only there if you enter a date before 1973.


I'm not sure I understand what you're trying to achieve in those first 8 columns.

The original request was to find a % for a list of years given a specific year and if not present pick the latest % before the given year. I confirmed VLOOKUP would work and provided an example.

The 1st revision said interest rates change monthly so you want multiple entries for a year. I advised the same 2 column solution worked if you showed change year as change date and the given year changed to a given date.

The 2nd revision asked that a table by year with months be used instead of a list. I advised INDEX and MATCH would be preferred over VLOOKUP and demonstrated with a worked example. Let me split that solution into its component parts, using the rows and columns from your latest example:

The IFERROR is only there to capture dates earlier than 1973 so let me remove it.

• =IF(ISNA(MATCH(YEAR($Q$10),$AB$19:$AB$25,0)),
  Checks for an exact match of the year in the table. If there's no exact match then we know we should use December values from the previous year listed.
• INDEX($AN$19:$AN$25,MATCH(YEAR($Q$10),$AB$19:$AB$25,TRUE)),
  This executes if no exact match of the year was found so it INDEXes into column AN (December values) for an earlier year using does a MATCH with TRUE (e.g. a 1996 year will return the row for 1992 so I get the 1992 %)
• INDEX($AC$19:$AN$25,MATCH(YEAR($Q$10),$AB$19:$AB$25,0),MONTH($Q$10)))
  This executes if an exact match of year was found so we know we'll have year and month in the table. It looks at all rows and columns in the array and selects the row matching on year and the column relating to the given month (e.g. a date of any June 1992 day will match the 3rd row and go to the 6th column, so retrieving 72% from AH21.

Looking at your formula after removing IFERROR for testing:

• =IF(ISNA(MATCH(YEAR($Q$10),$AB$19:$AB$25,0)),
  Same as my formula.
• INDEX($AB$19:$AN$25,MATCH(YEAR($Q$10),$AB$19:$AB$25,TRUE)),
  This executes if an exact year is not found. Here you're INDEXing into an array of rows 29 to 35 and columns AB to AN but only specifying a row number so you will get a #REF error.
• INDEX($AC$19:$AN$25,MATCH(YEAR($Q$10),$AB$19:$AB$25,0),MONTH(TODAY())))
  This executes when an exact year match is found but the column it uses for month is based on TODAY's date, not the entered date.

 

[rcomag]

hello again, sorry if i cannot fully express what i am trying to do, here is what i did after using the formula. the interest rate from FORMULA for 2018 is 16% as per year and date (reference month) when it should be 40% as per year and month with reference to the table. same with 2017 which is 16% but should be 64%. looking at the formula, only the reference month is included, the reference year is not. could the reference year and reference month be used in the computation. thank you

							REFERENCE MONTH
							August 27, 2019
								10,153.04
								REFERENCE TABLE
					INTEREST RATE as to year due and date of payment AS PER TABLE
			`			correct interest rate for year payment and as date of payment
					interest RATE				january	february	march	april	may	june	july	august	september	october	november	december
	REFERENCE YEAR AMOUNT	from FORMULA	INTEREST	TOTAL		1973	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%
252,470.00		2019	2019	2,524.70	0.16	16%	403.95	2,928.65	1974	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%	24%
23,640.00		2018	2018	236.40	0.16	40%	94.56	330.96	1992	72%	72%	72%	72%	72%	72%	72%	72%	72%	72%	72%	72%
23,640.00		2017	2017	236.40	0.16	64%	151.30	387.70	2017	50%	52%	54%	56%	58%	60%	62%	64%	66%	68%	70%	72%
23,640.00		2001	2016	3,782.40	0.16	72%	2,723.33	6,505.73	2018	26%	28%	30%	32%	34%	36%	38%	40%	42%	44%	46%	48%
									2019	-10%	-10%	-10%	8%	10%	12%	14%	16%	18%	20%	22%	24%
		TOTAL	6,779.90			3,373.14	10,153.04	2020	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%	-20%
=IFERROR(IF(ISNA(MATCH(YEAR($Q$10),$AB$19:$AB$25,0)),INDEX($AB$19:$AN$25,MATCH(YEAR($Q$10),$AB$19:$AB$25,TRUE)),INDEX($AC$19:$AN$25,MATCH(YEAR($Q$10),$AB$19:$AB$25,0),MONTH(TODAY()))),0)
=IF(ISNA(MATCH(YEAR($Q$10),$AB$19:$AB$25,0)),INDEX($AB$19:$AN$25,MATCH(YEAR($Q$10),$AB$19:$AB$25,TRUE)),INDEX($AC$19:$AN$25,MATCH(YEAR($Q$10),$AB$19:$AB$25,0),MONTH(TODAY())))

<colgroup><col><col><col><col><col span="2"><col><col><col><col><col span="12"></colgroup><tbody>
</tbody>

 

[Peter_SSs]

@rcomag
Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

Note that posts from new members are sometimes 'held back' until they are approved by a moderator. That had happened with the above post (& the 4 extra copies you made, and one a day or so ago) and you would have been presented with a message to that effect. So in future, please look out for such messages and in any case be patient!