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%))))))

 

[rcomag]

sorry, i just thought that the reply i made was not posted.

 

[Toadstool]

ecomag,

Forget the formula for a moment and instead please explain where your expected results come from.

						correct interest rate for year payment and as date of payment
		REFERENCE YEAR AMOUNT	interest rate from FORMULA	INTEREST
252,470.00	A	2019	2019	2,524.70	0.16	16%
23,640.00	B	2018	2018	236.4	0.16	40%
23,640.00	C	2017	2017	236.4	0.16	64%
23,640.00	D	2001	2016	3,782.40	0.16	72%

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

Let's skip Row A.

Row B has the same year, 2018, twice and I don't know why. The 40% expected could only be retrieved from your test data if you you were looking for August 2018. So are you saying the $Q$10 date of 27-Aug-19 should have the day and year ignored as you were only trying to specify a month to use?

Row C has the same questions. Why 2017 twice? Should it be retrieving $Q$10 month of the first 2017 or the second?

Row D has 2001 and 2016 years but why? Yes, I understand 72% is from the last entry for 1992 but why two years and no month?

 

[rcomag]

ecomag,

Forget the formula for a moment and instead please explain where your expected results come from.

						correct interest rate for year payment and as date of payment
		REFERENCE YEAR AMOUNT	interest rate from FORMULA	INTEREST
252,470.00	A	2019	2019	2,524.70	0.16	16%
23,640.00	B	2018	2018	236.4	0.16	40%
23,640.00	C	2017	2017	236.4	0.16	64%
23,640.00	D	2001	2016	3,782.40	0.16	72%

<tbody>
</tbody>

Let's skip Row A.

Row B has the same year, 2018, twice and I don't know why. The 40% expected could only be retrieved from your test data if you you were looking for August 2018. So are you saying the $Q$10 date of 27-Aug-19 should have the day and year ignored as you were only trying to specify a month to use?

Row C has the same questions. Why 2017 twice? Should it be retrieving $Q$10 month of the first 2017 or the second?

Row D has 2001 and 2016 years but why? Yes, I understand 72% is from the last entry for 1992 but why two years and no month?

i was reffering to this table of penalties

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%

<tbody>
</tbody>

40% on year 2018 because payment made is august 2019 (Q10), as per table (highlighted red). same as year 2017 which 64%. 2 columns for year because, from to to (2001-2016).

 

[Toadstool]

OK, so the year in Q10 is not used. You just want a way of specifying the month to select from columns AC to AN.

I've removed the first set of years as we can't calculate for a range. The formula below takes the reference year from column A and the month from $Q$10 to retrieve a percentage from the table:

	A	B	C	D	E	F	G
1	REFERENCE YEAR	AMOUNT	from original FORMULA	Expected INTEREST	New FORMULA		TOTAL
2	2019	2,524.70	0.16	0.16	0.16	403.95	2,928.65
3	2018	236.40	0.16	0.4	0.4	94.56	330.96
4	2017	236.40	0.16	0.64	0.64	151.30	387.70
5	2016	3,782.40	0.16	0.72	0.72	2,723.33	6,505.73
6
7	TOTAL	6,779.90				3,373.14	10,153.04

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula E2 =IF(ISNA(MATCH(A2,$AB$19:$AB$25,0)),INDEX($AN$19:$AN$25,MATCH(A2,$AB$19:$AB$25,TRUE)),INDEX($AC$19:$AN$25,MATCH(A2,$AB$19:$AB$25,0),MONTH($Q$10))) F2 =E2*B2 G2 =F2+B2 B7 =SUM(B2:B6) F7 =SUM(F2:F6) G7 =SUM(G2:G6) <tbody> </tbody>

<tbody>
</tbody>

 

[rcomag]

hello, tried the formula you have given me and its working. will try to analyze and learn more about the functions (isna), (match), (index) since i don't have any idea how it works. thank you very much. hopefully can learn more new things from you about the various excel formulas that i have no knowledge about and may help me in the future. again, big thanks to you.

 

[Toadstool]

You're welcome!