Throughout your life, it’s likely that you’re going to leverage various loans. Your first loan may be a credit card. If you go to college, you may need to get a student loan. Then, if you buy a home, you may get a mortgage. Each time I’ve found myself in need of a loan, I’ve Googled something like, “loan calculator for student loan” or “mortgage calculator” or “business loan calculator.” The results will be a series of random CRUD apps that happen to have the best SEO at the time, where you fill out some fields and the app spits out an answer. I figured there has to be a better way to use loan calculators.
While not a horrible way to go, my first problem with the CRUD approach is that you never see how they calculate and derive the fields (monthly payments, total interest paid, number of payments you will make, etc.). The second problem with this approach is that it’s hard to integrate this information to your personal P&L or net worth calculations, which may be derived in a completely different CRUD app (like NerdWallet) or maybe your own spreadsheets. Lastly, if you want to keep the analysis, you end up needing to create an account for a tool you’ll never use for anything other than the loan calculator. If you don’t want to make an account, your only option is taking a screenshot of the result to save for later.
Loan calculators become more powerful and useful if they’re built in a spreadsheet and shared/reused like an app. This way, you can combine the results with another spreadsheet, make your own edits to the analysis, and fully trace how the calculations work.
To be fair, theres a reason why this approach hasn’t taken off. It’s not because spreadsheet templates for loan calculators don’t exist. It’s because in current spreadsheets, they’re far more complicated than CRUD apps to update and maintain. In most current loan calculator templates, you have to update formula ranges and actually manipulate the analysis to get the answer you’re looking for. Depending on how the calculator is built, you may have to recreate your own timeline, whether it’s a one year loan or a 30 year loan, by dragging the monthly analysis right or down 12 (for one year) or 360 (for 20 years) columns or rows.
Below is a simple example of a loan calculator that we created using Subset. It combines the simplicity and ease of a CRUD app with the flexibility and familiarity of spreadsheet.
Solutions like this is what I’ve wanted to see when I Google for templates online. A few of the benefits over CRUD apps and traditional spreadsheets are the following.
1. Ability to change inputs and update analysis without updating formulas or data ranges.
2. Ability to audit and trace how cells are calculated to make changes if needed.
3. Ability to use analysis as a part of another spreadsheet (export as spreadsheet or use directly).
4. Ability to “fork the analysis,” make changes and share a new version for the community to use
