Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.

Reply
GINMED
Helper III
Helper III

Correct Beginning balance on loan schedule

Hi,

I need your advice, how to correctly calculate beginning balance in lean amortization schedule.

Therre is my data, wich has interest payment, but there is unusually case - 1st interest payment is currnet amount.

IDStart DateEnd DatePV1st interest pamentRateFreq of paymentRate in period
11111113.04.202112.12.20236001,9961,490,20489120,017074167

 

There is may measure for beginning balance, but it does not calculate correct interest payments depending on 1st payment :

 

Beginning Balance =
Var PV = sum('Table'[PV])
Var Interest = SELECTEDVALUE('Table'[Rate in period])
Var Index = SELECTEDVALUE('Project'[Sequence])
Var Payment = MIN('Table'[Rent payment])
Var Result =
             if(PV *
                     POWER1 + InterestIndex - 1 ) - Payment * DIVIDEPOWER1 + InterestIndex1 ) - 1 , Interest) >= 0,
                PV *  POWER1 + Interest , Index - 1 ) - Payment * DIVIDEPOWER1 + InterestIndex1 ) - 1 ,Interest),0RETURN Result

 

 

Expected result:

SequenceStarting BalanceInterestPrincipalEnding Balance
16001,9925961,49214,075849,41259
25849,4125999,87356,785592,50259
35592,5025995,49356,785331,21259
45331,2125991,03356,785065,46259
55065,4625986,49356,784795,17259
64795,1725981,87356,784520,26259
74520,2625977,18356,784240,66259
84240,6625972,41356,783956,29259
93956,2925967,55356,783667,06259
103667,0625962,61356,783372,89259
113372,8925957,59356,783073,70259
123073,7025952,48356,782769,40259
132769,4025947,29356,782459,91259
142459,9125942356,782145,13259
152145,1325936,63356,781824,98259
161824,9825931,16356,781499,36259
171499,3625925,6356,781168,18259
181168,1825919,95356,78831,3525902
19831,352590214,18356,78488,7525902
20488,75259028,34356,78140,3125902
21140,31259022,4142,710,002590196

 

How can I switch interest payments in my schedule project to get correct beggining balance??

 

Thaks in advance

1 REPLY 1
GINMED
Helper III
Helper III

GINMED_3-1675334880162.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors