HELP: Excel Formula using date as basis for interest

rcomag

rcomag

New Member
Joined
Aug 23, 2019
Messages
37
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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%))))))

:biggrin::biggrin::biggrin:
 
Sort by date Sort by votes
sorry, i just thought that the reply i made was not posted.
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
ecomag,

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

[TABLE="width: 584"]
<colgroup><col><col span="4"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]correct interest rate for year payment and as date of payment[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]REFERENCE YEAR AMOUNT
[/TD]
[TD]interest rate from FORMULA[/TD]
[TD]INTEREST[/TD]
[/TR]
[TR]
[TD="align: right"]252,470.00[/TD]
[TD="align: center"]A
[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]2019
[/TD]
[TD="align: right"]2,524.70[/TD]
[TD]0.16[/TD]
[TD]16%[/TD]
[/TR]
[TR]
[TD="align: right"]23,640.00[/TD]
[TD="align: center"]B
[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]236.4[/TD]
[TD]0.16[/TD]
[TD]40%[/TD]
[/TR]
[TR]
[TD="align: right"]23,640.00[/TD]
[TD="align: center"]C
[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]236.4[/TD]
[TD]0.16[/TD]
[TD]64%[/TD]
[/TR]
[TR]
[TD="align: right"]23,640.00[/TD]
[TD="align: center"]D
[/TD]
[TD="align: right"]2001[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]3,782.40[/TD]
[TD]0.16[/TD]
[TD]72%[/TD]
[/TR]
</tbody>[/TABLE]

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?
 
Upvote 0
Toadstool said:
ecomag,

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

[TABLE="width: 584"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]correct interest rate for year payment and as date of payment[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]REFERENCE YEAR AMOUNT[/TD]
[TD]interest rate from FORMULA[/TD]
[TD]INTEREST[/TD]
[/TR]
[TR]
[TD="align: right"]252,470.00[/TD]
[TD="align: center"]A
[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]2,524.70[/TD]
[TD]0.16[/TD]
[TD]16%[/TD]
[/TR]
[TR]
[TD="align: right"]23,640.00[/TD]
[TD="align: center"]B
[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]236.4[/TD]
[TD]0.16[/TD]
[TD]40%[/TD]
[/TR]
[TR]
[TD="align: right"]23,640.00[/TD]
[TD="align: center"]C
[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]236.4[/TD]
[TD]0.16[/TD]
[TD]64%[/TD]
[/TR]
[TR]
[TD="align: right"]23,640.00[/TD]
[TD="align: center"]D
[/TD]
[TD="align: right"]2001[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]3,782.40[/TD]
[TD]0.16[/TD]
[TD]72%[/TD]
[/TR]
</tbody>[/TABLE]

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?
Click to expand...



i was reffering to this table of penalties

[TABLE="class: cms_table, width: 885"]
<tbody>[TR]
[TD="colspan: 3"]TABLE OF PENALTIES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]january[/TD]
[TD]february[/TD]
[TD]march[/TD]
[TD]april[/TD]
[TD]may[/TD]
[TD]june[/TD]
[TD]july[/TD]
[TD]august[/TD]
[TD]september[/TD]
[TD]october[/TD]
[TD]november[/TD]
[TD]december[/TD]
[/TR]
[TR]
[TD="align: right"]1973[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]12%[/TD]
[/TR]
[TR]
[TD="align: right"]1974[/TD]
[TD="align: right"]24%[/TD]
[TD="align: right"]24%[/TD]
[TD="align: right"]24%[/TD]
[TD="align: right"]24%[/TD]
[TD="align: right"]24%[/TD]
[TD="align: right"]24%[/TD]
[TD="align: right"]24%[/TD]
[TD="align: right"]24%[/TD]
[TD="align: right"]24%[/TD]
[TD="align: right"]24%[/TD]
[TD="align: right"]24%[/TD]
[TD="align: right"]24%[/TD]
[/TR]
[TR]
[TD="align: right"]1992[/TD]
[TD="align: right"]72%[/TD]
[TD="align: right"]72%[/TD]
[TD="align: right"]72%[/TD]
[TD="align: right"]72%[/TD]
[TD="align: right"]72%[/TD]
[TD="align: right"]72%[/TD]
[TD="align: right"]72%[/TD]
[TD="align: right"]72%[/TD]
[TD="align: right"]72%[/TD]
[TD="align: right"]72%[/TD]
[TD="align: right"]72%[/TD]
[TD="align: right"]72%[/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]52%[/TD]
[TD="align: right"]54%[/TD]
[TD="align: right"]56%[/TD]
[TD="align: right"]58%[/TD]
[TD="align: right"]60%[/TD]
[TD="align: right"]62%[/TD]
[TD="align: right"]64%[/TD]
[TD="align: right"]66%[/TD]
[TD="align: right"]68%[/TD]
[TD="align: right"]70%[/TD]
[TD="align: right"]72%[/TD]
[/TR]
[TR]
[TD="align: right"]2018[/TD]
[TD="align: right"]26%[/TD]
[TD="align: right"]28%[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]32%[/TD]
[TD="align: right"]34%[/TD]
[TD="align: right"]36%[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]42%[/TD]
[TD="align: right"]44%[/TD]
[TD="align: right"]46%[/TD]
[TD="align: right"]48%[/TD]
[/TR]
[TR]
[TD="align: right"]2019[/TD]
[TD="align: right"]-10%[/TD]
[TD="align: right"]-10%[/TD]
[TD="align: right"]-10%[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"]16%[/TD]
[TD="align: right"]18%[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]22%[/TD]
[TD="align: right"]24%[/TD]
[/TR]
[TR]
[TD="align: right"]2020[/TD]
[TD="align: right"]-20%[/TD]
[TD="align: right"]-20%[/TD]
[TD="align: right"]-20%[/TD]
[TD="align: right"]-20%[/TD]
[TD="align: right"]-20%[/TD]
[TD="align: right"]-20%[/TD]
[TD="align: right"]-20%[/TD]
[TD="align: right"]-20%[/TD]
[TD="align: right"]-20%[/TD]
[TD="align: right"]-20%[/TD]
[TD="align: right"]-20%[/TD]
[TD="align: right"]-20%[/TD]
[/TR]
</tbody>[/TABLE]

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).
 
Upvote 0
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:


ABCDEFG
AMOUNTfrom original FORMULAExpected INTERESTNew FORMULATOTAL

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]REFERENCE YEAR[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]2019[/TD]
[TD="align: right"]2,524.70[/TD]
[TD="align: right"]0.16[/TD]
[TD="align: right"]0.16[/TD]
[TD="align: right"]0.16[/TD]
[TD="align: right"]403.95[/TD]
[TD="align: right"]2,928.65[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2018[/TD]
[TD="align: right"]236.40[/TD]
[TD="align: right"]0.16[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]94.56[/TD]
[TD="align: right"]330.96[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]2017[/TD]
[TD="align: right"]236.40[/TD]
[TD="align: right"]0.16[/TD]
[TD="align: right"]0.64[/TD]
[TD="align: right"]0.64[/TD]
[TD="align: right"]151.30[/TD]
[TD="align: right"]387.70[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]2016[/TD]
[TD="align: right"]3,782.40[/TD]
[TD="align: right"]0.16[/TD]
[TD="align: right"]0.72[/TD]
[TD="align: right"]0.72[/TD]
[TD="align: right"]2,723.33[/TD]
[TD="align: right"]6,505.73[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]TOTAL[/TD]
[TD="align: right"]6,779.90[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3,373.14[/TD]
[TD="align: right"]10,153.04[/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD]Cell[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=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)))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=E2*B2[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=F2+B2[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B7[/TH]
[TD="align: left"]=SUM(B2:B6)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F7[/TH]
[TD="align: left"]=SUM(F2:F6)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G7[/TH]
[TD="align: left"]=SUM(G2:G6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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. :) ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Yearly Interest Calculation
Replies
1
Views
334
signup
S
F
  • Solved
Formula determine if a date falls between a specific M/D range for multiple years for YTD comparison.
Replies
6
Views
937
flashgordie
F
M
  • Question Question
Excel Amortization - Calculation help
Replies
4
Views
753
StephenCrump
S
K
  • Question Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
533
Eric W
Eric W
S
  • Solved
VLOOKUP Formula help?
Replies
6
Views
444
dreid1011
dreid1011

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top