Formula for Interest calculation on reducing balance method without monthly schedule

A

aaleem

Board Regular
Joined
Sep 26, 2014
Messages
56
Office Version
  1. 2016
HI,

I have no. of leases which I want to calculate the interest on reducing balance method based on the start date and end date for a specific period within the lease term. for each lease, I do not want to create a monthly schedule. Is there any formula where I can drive the interest value based on the reducing the balance method for a specific period?

I'm attaching a sample datasheet.

any help will be appreciated.

regards
aleem
Book1 - 2021-07-06T233853.913.xls
ABCDEFGHIJKL
1PeriodSchedule Begin DateBeginning BalancePayment AmountInterest AmountReduction AmountEnding BalanceCur Cod
265134.4365134.43USDAmount$ 65,134.43
3101-04-202165134.431500271.391228.6163905.83USDAnnual %5%
4201-05-202163905.831500266.271233.7362672.1USDTerm in years4
5301-06-202162672.11500261.131238.8761433.23USDStart Date01-03-21
6401-07-202161433.231500255.971244.0360189.21USDPayment type:End of the Period
7501-08-202160189.211500250.791249.2158940USDPayment frequency:Monthly
8601-09-2021589401500245.581254.4257685.58USDInterest Compounding Frequency:Monthly
9701-10-202157685.581500240.361259.6456425.94USD
10801-11-202156425.941500235.111264.8955161.04USD
11901-12-202155161.041500229.841270.1653890.88USDScheduled Monthly payment:$ 1,500.00
121001-01-202253890.881500224.551275.4552615.43USDScheduled number of payments:48
131101-02-202252615.431500219.231280.7751334.66USDInterest Rate(Per Period):0.004166667
141201-03-202251334.661500213.891286.1150048.55USD
151301-04-202250048.551500208.541291.4648757.09USD
161401-05-202248757.091500203.151296.8547460.24USD
171501-06-202247460.241500197.751302.2546157.99USDInterest for period
181601-07-202246157.991500192.321307.6844850.32USD
191701-08-202244850.321500186.881313.1243537.19USDFrom Period01-04-21
201801-09-202243537.191500181.41318.642218.6USDto Period30-06-21
211901-10-202242218.61500175.911324.0940894.51USD
222001-11-202240894.511500170.391329.6139564.9USDInterest amount1054.76<How do I get this value by formula
232101-12-202239564.91500164.851335.1538229.76USDWithout creating a monthly schedule
242201-01-202338229.761500159.291340.7136889.05USD
252301-02-202336889.051500153.71346.335542.75USD
262401-03-202335542.751500148.091351.9134190.85USD
272501-04-202334190.851500142.461357.5432833.31USD
282601-05-202332833.311500136.811363.1931470.11USD
292701-06-202331470.111500131.131368.8730101.24USD
302801-07-202330101.241500125.421374.5828726.66USD
312901-08-202328726.661500119.691380.3127346.36USD
323001-09-202327346.361500113.941386.0625960.3USD
333101-10-202325960.31500108.171391.8324568.47USD
343201-11-202324568.471500102.371397.6323170.84USD
353301-12-202323170.84150096.551403.4521767.38USD
363401-01-202421767.38150090.71409.320358.08USD
373501-02-202420358.08150084.831415.1718942.9USD
383601-03-202418942.9150078.931421.0717521.83USD
393701-04-202417521.83150073.011426.9916094.84USD
403801-05-202416094.84150067.061432.9414661.9USD
413901-06-202414661.9150061.091438.9113222.99USD
424001-07-202413222.99150055.11444.911778.09USD
434101-08-202411778.09150049.081450.9210327.16USD
444201-09-202410327.16150043.031456.978870.19USD
454301-10-20248870.19150036.961463.047407.15USD
464401-11-20247407.15150030.861469.145938.02USD
474501-12-20245938.02150024.741475.264462.76USD
484601-01-20254462.76150018.591481.412981.35USD
494701-02-20252981.35150012.421487.581493.78USD
504801-03-20251493.7815006.221493.78USD
Sheet1
Cell Formulas
RangeFormula
K2K2=+C2
K11K11=+D3
K13K13=+K3/12
K22K22=SUM(E3:E6)
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
To return the cumulative interest paid between two periods, use the function CUMIPMT...

=CUMIPMT(5%/12,48,65134.43,1,4,0)

...where the number 1 specifies the first period and the number 4 specifies the fourth period.

Based on your sample worksheet, though, first replace 30-06-21 in cell K20 with 01-07-21, and then try...

=CUMIPMT(K3/12,K12,K2,MATCH(K19,B3:B50,0),MATCH(K20,B3:B50,0),0)

Hope this helps!
 
Upvote 0
Solution
Thank you so much Domenic, yes this is perfect.

I followed your guidance of replacing period 1 and with end number of the period with YEARFRAC function and got the results
=CUMIPMT(K3/12,K12,K2,1,YEARFRAC(K19,K20)*12+1,0)

kind regards
aleem
 
Upvote 0
That's great, glad you were able to adopt it to suit your needs. And thanks for the feedback.

Cheers!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
81
etaf
etaf
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
357
kevin9999
K
J
  • Solved
Add 1 day to a date within a range as long as 2 conditions are met [without macros]
Replies
4
Views
554
juanam
J
M
  • Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
203
Peter_SSs
Peter_SSs
G
  • Solved
Counting zero billing days in Excel until current date
Replies
5
Views
496
awoohaw
awoohaw

Forum statistics

Threads
1,214,430
Messages
6,119,442
Members
448,898
Latest member
drewmorgan128

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