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:

`KnownDownPayment`

(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:

`!KnownDownPayment`

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
mortgage. Type `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`