One of the most common uses of spreadsheets is to perform calculations related to loans. It’s no wonder, then, that Calcapp is heavily used for this specific purpose.
This sample app is used to calculate mortgages. The user enters the value of the mortgage, the down payment amount, the tenure in years and finally the annual interest rate. The app then calculates the monthly installment (the amount due each month), the total sum of the payments made and the cost of the mortgage.
Here’s a screenshot:
Run the app here. This app is available as the Sample: Mortgage template in Calcapp Creator and you are encouraged to experiment with it as you read this blog post.
The input fields
The first field of the app, Type, is a drop-down field denoting the property type with three values, Apartment, Mini-condo and Condominium. The value selected by users is not part of any calculations, but will be part of any report sent. The Value field holds the value of the property. Initially, it is set to $200,000.
The Mortgage information group has all other input fields related to the mortgage. The first field, Known down payment? is a switch field, and as a result is either TRUE or FALSE. The user is expected to fill out whether the down payment amount is known.
If the down payment is not known, two fields appear. The Desired LTV ratio is a percentage — LTV stands for Loan To Value. 0% means that the loan will be for the full value of the property with no down payment, 70% means that the loan will only be for 30% of the loan. The Required down payment field then displays the down payment amount using the following formula:
Value * (1 - DesiredLTVRatio)
If the down payment is known, the Desired down payment field appears instead, where the down payment amount can be entered directly.
The Desired LTV ratio and Required down payment fields are only shown when the Known down payment? field is FALSE. Consequently, the following formula is associated with their Visible properties:
(The ! symbol means “not.” The formula
NOT(KnownDownPayment) is equivalent.)
Associate a formula with the Visible property by clicking the fx button next to the Visible property in the inspector when the relevant field is selected.
The Desired down payment field is only visible if the Known down payment? field is TRUE. As such, this is the formula associated with its Visible property:
The Mortgage amount field displays the mortgage amount. Its formula needs to subtract the down payment amount from the property value. To determine if the Required down payment field or the Desired down payment field should be used to determine the down payment amount, it needs to consult the Known down payment? field. It does so using the IF formula function:
IF(KnownDownPayment, Value - DesiredDownPayment, Value - RequiredDownPayment)
The Tenure field denotes the number of years it takes to fully pay off the
years into the Tenure field (to the right of the label) to
make that the unit of the field (ensuring that “ years” appears after any number
the user enters).
The Annual interest rate field is a percent field, scaling values automatically. That means that if a user enters 15%, the value you’re using in calculations is 0.15. Press the % button in the inspector when the field is selected to make it a percent field.
The output fields
The Monthly installment field denotes the amount due each month. Its formula uses the PMT formula function, which is an oft-used function in the world of spreadsheets:
PMT(AnnualInterestRate / 12, Tenure * 12, -MortgageAmount)
The PMT function returns the periodic payment for a loan. The first parameter is the interest rate and the second parameter is the total number of payments for the loan. The third parameter is the value of the loan (sometimes called the present value or the principal). It is important that all parameters use the same unit, which in this case should be a month.
To provide the monthly interest rate, we need to divide the annual interest rate by 12. Similarly, the total number of payments should be the total number of months, which we get by multiplying the (yearly) tenure by 12. Finally, the loan amount given as the third parameter is made negative. This is done to ensure that the returned result is positive.
There are other parameters to the PMT function which we don’t use here, as we want the mortgage to be fully paid off and payments to be due at the end of every month, not the beginning. To learn more about the PMT function, refer to Calcapp Creator’s reference sidebar.
The Total payments field denotes the total payments made over the course of the mortgage. Its formula simply multiplies the monthly payment by 12 to get the yearly cost, and then multiplies that number with the number of years it takes to pay off the mortgage:
MonthlyInstallment * 12 * Tenure
Finally, the Total interest payments field tells you the cost of the mortgage itself. It simply subtracts the mortgage amount from the property value:
TotalPayments - MortgageAmount