Get a copy of your own Amortization Schedule in Google Sheets [click here]. Copy to your own Google account to make it your own.
The first question you may ask when you decide to get a loan to purchase a Church building is usually centered around the monthly mortgage payment. It’s vital to set your budget, so it makes sense. This is why I decided to put a lesson together on the Church Mortgage Loan Calculator.
From a simple web search, you can find many mortgage payment calculators. In this lesson, I will cover the components of calculating your mortgage payment. I’ll also show you how to create your payment calculator in Excel and finally discuss some essential financial items that lenders look at that you should know about.
A basic mortgage calculator can give you a monthly payment with just three items, the loan amount, interest rate, and amortization term. Some offer graphs to show you the relationship between the loan balance and interest paid over time. One key figure for church loans is the loan term (“loan period”).
I will do my best to keep this part from being confusing. The problem is that “term” and “terms” refer to a few things. The key is the context.
Loan Term or Loan Maturity
The loan term refers to the length of the loan before it matures. Because of the heightened risk in commercial lending, it is not typical for the loan maturity to match the amortization period (“fully amortized”).
The standard conventional loan maturity options offered are three years, five years, seven years, and ten years. To help offset the risks of long-term maturities and offer terms more favorable to borrowers, some banks provide SWAP loans. Sparing you the details on how they work, the maturity term on SWAP loans can go up to 20 years. This allows the lender to review the strength of the Church and set the loan terms to match the current financial stability, market conditions, and internal costs.
When a commercial loan matures, the loan is due in full. Whatever the balance is at that time is the amount due. Typically, your bank will work with you before the loan matures to refinance the loan, pushing out the maturity.
For this reason, I plan to connect with my clients at least once a quarter. This allows me to get an update on how things are going for the Church, discuss what I see in the market, and help them prepare for any upcoming changes, such as a loan maturity. At the time of writing this, interest rates are rising. It is essential to have early, consistent conversations around pricing, which we will discuss later.
While the loan term refers to when a loan matures, the amortization period refers to the period used to set the loan payment amount. Depending on regulations and the bank’s risk appetite, you might expect to see 25 or 30-year amortization available.
The longer the amortization period, the lower the monthly payment. However, this also means you will pay more interest over the loan’s lifetime. While this might seem like a bad thing, I suggest looking at the short and long-term goals of the Church. Depending on the situation, shortening the amortization period might not offer as many benefits to you as a more extended amortization period.
A shorter amortization period will reduce interest costs, but an extended amortization period will require a lower monthly payment. Each situation is different, and the options should be weighed accordingly.
The interest rate is probably the most talked about component of the loan payment calculation. While this can set the cost of the loan, as we can see above, the amortization period also has an impact.
Just about all interest rates are set by summing an index and margin. The index is a market investment that banks use as a benchmark, and the margin is the amount needed to cover operating costs, including profit.
Common indexes used are the US Treasury and Wall Street Journal Prime Rate (“Prime Rate”). An older index that is being phased out is the LIBOR. Along with the lesson tool, I have listed the current US Treasury rates and Prime Rate for you to review, updated daily.
Loan Amount, Loan Principal, or Loan Balance
All these terms mean the same thing, how much do you currently owe? You can find this on a monthly loan statement, view it on your lender’s web portal, or call them to find out.
While amortization and the interest rate play a factor in the payment, the loan amount is the most significant factor. I don’t know who said it, but I am reminded of the old saying, “junk in, junk out.” Many borrowers start with the interest rate, but you don’t have control over pricing. You have control over your budget and, ultimately, the chosen property.
Because it is so important to start with the right property, I created the Church Property Buyer Guide in addition to this lesson. In that lesson, I review a few topics to help you select a property that will fit your current and future needs.
A general rule of thumb for the down payment is 25% to 30% for traditional Church properties. Non-traditional properties, like prefab structure steel, remote, excess land, land, and more will affect the size of the down payment required.
The greater the down payment, the smaller the loan amount, and ultimately, the less interest you will pay over the life of the loan. I recommend putting down the most you can while maintaining funds for operations and reserves.
Finally, we arrive at the main component. Based on all the factors above, the loan payment is calculated. This final figure plays a crucial part in critical ratios: the biggest of which I will cover next, Debt Service.
The quickest way to create a payment calculator (“mortgage calculator”) is to use Excel or Google Sheets. There are many loan calculators online, but I like to have my own to double-check things.
You should note that this lesson is only a cursory overview of payment calculations. The actual payment amount in your loan docs will vary based on several variables. An online loan calculator, just like the tool in this lesson, is meant to provide a general idea.
Calculate Payments in Excel or Google Sheets
For anyone looking to go deeper, try making a payment calculator in Excel or Google Sheets. For either one, the formula is the same.
=-pmt(rate%/12, # periods*12, $principal)
For example, if the rate were 5.5%, the loan amortization was 25 years, and the principal loan balance was $1,000,000, the formula would look as follows.
=-pmt(5.5%/12, 25*12, 1000000)
Notice that I divided the interest rate by 12 to get a monthly rate. Next, since the amortization period is in years, and I need the total monthly payments throughout the amortization period, I multiplied the amortization period by 12.
The final value computed should be similar to the following.
The lesson would not be complete if I did not speak about Debt Service. The term refers to the annual payment, or commitment, for the loan request. From the example above, you could arrive at the Debt Service in two ways.
- Multiply the monthly payment by 12 or,=$6,140.87*12
- Do not divide the interest rate by 12, and do not multiply the amortization period by 12. If you go this route, the formula will look as follows=-pmt(rate%, # periods, $principal)or=-pmt(5.5%, 25, 1000000)
Due to rounding variances, each option will give a different answer, but they are close. It is up to you to decide which version you want to use.
One final note on the formula before I move to the next section. Due to my preference in presentation, I put a minus “-” sign before the formula. I’ll skip the technical reason, but Excel and Google Sheets provide a negative payment amount. The minus sign in front changes the final figure to a positive number. This is my preference. You are welcome to do it how you prefer.
Debt Service Ratio
You will want to calculate your debt service ratio when considering your financing options. It is a crucial part when you calculate vital ratios. All lenders will run your debt service in their “financial calculators” to see if your cash flow is sufficient to cover the debt service. This is called the Debt Service Ratio.
The Debt Service Ratio is calculated by dividing your total Church Cash Flow by the Debt Service. The goal is for your Church’s Cash Flow to exceed Debt Service. It breaks down the figures into simple numbers that are easily compared. Ideally, you will get a number equal to or greater than one.
=Church Cash Flow / Debt Service
From our example above, if we generated $90,000 in Church Cash Flow, our answer would be 1.21; calculated as follows.
=74,549.35 / 90,000
With a Debt Service Ratio of 1.21, the Church is generating an extra $0.21 or 21 cents for every dollar in loan payments due to the lender. The additional funds, in theory, are available to go towards reserves.
Operationally, it is healthy for any organization to generate enough income to cover operating costs and put funds away for a “rainy day.” An organization with a negative Debt Service Ratio is generally seen as two years from shutting down. Please note that negative net income does not mean your Debt Service Ratio will be negative.