In a recent post I showed math formulas for a pool of mortgages that could calculate any amount, for any month, without an amortization schedule. This post and spreadsheet takes these formulas one step further and creates names for the formulas. After entering a description of the loan pool:
Enter the month, in the future, that you want to get the values for:
Then pick any cell on any sheet and type in the name of the value you want, and you get the value for the above month. For example, using the 9th month chosen above, entering “=Defaults” gives the amount of defaults for that month ($3,759.69). Here are the named formulas:
The formulas that make up these names looks like this:
The input names looks like this:
In addition there is a sheet called “Proof” that is an amortization schedule, made up from the previous description of the pool that was entered. A lookup of the month chosen from the schedule, along with the row that uses the named formulas, match. This shows the named formulas are correct.