Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowGet inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.
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.
ID | Start Date | End Date | PV | 1st interest pament | Rate | Freq of payment | Rate in period |
111111 | 13.04.2021 | 12.12.2023 | 6001,99 | 61,49 | 0,20489 | 12 | 0,017074167 |
There is may measure for beginning balance, but it does not calculate correct interest payments depending on 1st payment :
Expected result:
Sequence | Starting Balance | Interest | Principal | Ending Balance |
1 | 6001,99259 | 61,49 | 214,07 | 5849,41259 |
2 | 5849,41259 | 99,87 | 356,78 | 5592,50259 |
3 | 5592,50259 | 95,49 | 356,78 | 5331,21259 |
4 | 5331,21259 | 91,03 | 356,78 | 5065,46259 |
5 | 5065,46259 | 86,49 | 356,78 | 4795,17259 |
6 | 4795,17259 | 81,87 | 356,78 | 4520,26259 |
7 | 4520,26259 | 77,18 | 356,78 | 4240,66259 |
8 | 4240,66259 | 72,41 | 356,78 | 3956,29259 |
9 | 3956,29259 | 67,55 | 356,78 | 3667,06259 |
10 | 3667,06259 | 62,61 | 356,78 | 3372,89259 |
11 | 3372,89259 | 57,59 | 356,78 | 3073,70259 |
12 | 3073,70259 | 52,48 | 356,78 | 2769,40259 |
13 | 2769,40259 | 47,29 | 356,78 | 2459,91259 |
14 | 2459,91259 | 42 | 356,78 | 2145,13259 |
15 | 2145,13259 | 36,63 | 356,78 | 1824,98259 |
16 | 1824,98259 | 31,16 | 356,78 | 1499,36259 |
17 | 1499,36259 | 25,6 | 356,78 | 1168,18259 |
18 | 1168,18259 | 19,95 | 356,78 | 831,3525902 |
19 | 831,3525902 | 14,18 | 356,78 | 488,7525902 |
20 | 488,7525902 | 8,34 | 356,78 | 140,3125902 |
21 | 140,3125902 | 2,4 | 142,71 | 0,002590196 |
How can I switch interest payments in my schedule project to get correct beggining balance??
Thaks in advance
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.