Forum Discussion
Amortization Schedule Help
I need help in Power bi to perform this for me and I am so confused on it. I have the follwing loan Info for several loans and I need Power BI to take each loan and pass through and generate a Amortization Schedule for it and then go to the next one and do the same with all of them and then I can get my total per month and year for what I have oustanding. My data Source is a SQL Database with read only Access.
LoanID : Bank: LoanAmount : LoanStart : InterestYearly: NumberOfMonths
11111 : HSS : 57799.04 : 04/04/2024 : 7.42 : 54
22222 : HSS : 34517.60 : 04/04/2024 : 1.09 : 48
Can anyone help me get this to work. I dont mind doing the legwork to learn but I am completely lost on this and Have been working on for about six months with all my ideas close but just not working. Example of Output desired:
| Payment Date | Month | Beginning Balance | Payment | Interest | Principle | Ending Balance |
| 6/15/2020 | 1 | $ 57,799.04 | $1,262.25 | $ 357.39 | $904.86 | $ 56,894.18 |
| 7/29/2020 | 2 | $ 56,894.18 | $1,262.25 | $ 351.80 | $910.45 | $ 55,983.73 |
| 8/29/2020 | 3 | $ 55,983.73 | $1,262.25 | $ 346.17 | $916.08 | $ 55,067.64 |
| 9/28/2020 | 4 | $ 55,067.64 | $1,262.25 | $ 340.50 | $921.75 | $ 54,145.90 |
| 5/29/2022 | 50 | $ 6,195.84 | $1,262.25 | $ 38.31 | $1,223.94 | $ 4,971.90 |
| 6/28/2022 | 51 | $ 4,971.90 | $1,262.25 | $ 30.74 | $1,231.51 | $ 3,740.40 |
| 7/29/2022 | 52 | $ 3,740.40 | $1,262.25 | $ 23.13 | $1,239.12 | $ 2,501.28 |
| 8/29/2022 | 53 | $ 2,501.28 | $1,262.25 | $ 15.47 | $1,246.78 | $ 1,254.49 |
| 9/28/2022 | 54 | $ 1,254.49 | $1,262.25 | $ 7.76 | $1,254.49 | $ (0.00) |
Thanks in advance for any help given.
3 Replies
Hi timsbrt
I'd suggest starting with the basics and looking up existing posts on this given that you may have a range of different factors that will influence how this is achieved.
Here are a few sources I recommend to help guide you through the process. I recommend starting with the first one given it's more of a tutorial style Youtube clip:
- https://www.youtube.com/watch?v=hZBQKUbLofE
- https://forum.enterprisedna.co/t/create-amortization-table/2404/3
- https://community.fabric.microsoft.com/t5/Desktop/loan-amortization/td-p/632336
Hope this provides a starting point.
All the best.
Theo
I would like to find someone to teach this to me. I am still not making sense of how to do this. I have tried so many ways and read so many articles that I am about to give up on this. ☹️ theoC thanks for the reading but i am just not connecting the dots.
