A few weeks ago, I was paying my mortgage and like any good accountant, I was referencing my homemade Excel loan amortization schedule. Did you just fall asleep? No, it’s really cool! It can tell you all kinds of useful information. Can you tell I’ve rehearsed this line on my wife many times?
If you are wondering what an amortization schedule is, it basically shows the status of your loan by time period. You can see the amount of Principal and Interest you are paying on the loan, etc.
Anyway, wouldn’t it be nice to have this in Tableau? I could just load in the spreadsheet and start visualizing the data, but that wouldn’t be useful for anyone else. I really wanted a Tableau mortgage calculator. And, I wanted it to be mobile friendly. And, I still wanted my amortization schedule. Is Tableau the right tool for the job? Probably not, but it sounded like a fun challenge.
I knew I would need one record per period so BOOM, I had my underlying data for Tableau. Just one column of data. The column was called Period and the values were 1:600. I chose 600 so you could go up to 50 years. I’m not sure you can even get a loan with that term, but why not? The rest of the data was going to come from YOU via parameters and table calculations!
Put on your dork hat, here we go!
Setup a bunch of Parameters
- I made most of these direct type
- I formatted them so they would display as currency, percentage, etc.
Start building your Table calculations
- Due Date, Beginning Balance, Interest and Principal are all table calcs
- I could simply calculate a snap shot of the loan, but I want to be able to see the entire schedule
Things were coming along great! I had my first row of the table populated, now it was was just a matter of telling my Beginning Balance to pull from the previous Ending Balance. Then, I was stuck, for awhile! I got the dreaded circular reference.
I was calculating the Ending Balance by subtracting the monthly payment from the Beginning Balance.
My Beginning Balance was the previous row’s ending balance. Now what?
After scratching my head for a while…I figured I’ll just calculate the outstanding balance of the loan directly without doing the simple arithmetic across the row and avoid the circular reference. Easy right?
Here is what it looks like in Tableau:
Now, I was back in business! I was able to fill out the entire amortization schedule and visualize the loan.
I wanted to make it so you could enter a prepayment amount each month and see how much interest you saved, but something about building 600 parameters seemed inefficient. I think I’ll just keep that in Excel!
This was a fun exercise in table calculations. I wonder what else we can build from nothing?
Give the interactive version below a shot and see if it works for you. Thanks for reading.