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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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

L
  • Solved
Help Please
2 3
Replies
21
Views
405
Asbestos_Jen
Asbestos_Jen
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
235
dshtesy
D
V
  • Question Question
US/European Date Format Updates Not Working on Macro Run
Replies
0
Views
53
vancher19
V
K
  • Solved
Evaluate date range and return the corresponding month and year
Replies
1
Views
130
Scott Huish
Scott Huish
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
228
Raj1979
R

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

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