A Feasibility Study: Loan Amortization
The main goals of this tutorial are: to construct a loan amortization table, and integrate such into the business plan we made before. When we obtain loans, we make periodic -mostly monthly- installments or payments. Banks or financial institutions charge interest as a price of lending money, and as a return for their loan services. The payment of a loan is composed of interest and repayment of the original principal or amount. For example, if you obtain a loan of 20,000 Euros, and you are required to pay an installment of €1000 per month, part of this 1000 accounts for interest expense and the remaining goes to repay the original amount of the loan, which is €20,000 in this particular case. That is, if the yearly interest rate is 5%, then, €83.33 of the first €1000 payment is paid as interest, and the remaining €916.67 are paid as repayment of the loan. The €83 is calculated as the remaining of the loan which is 20,000 at start multiplied by the interest rate divided by 12, because the interest rate is given on yearly basis, whereas the loan payment is made on monthly basis. The loan payment is a function of the loan amount, interest rate, and number of periods or payments. We need all these three variables in order to calculate the payment. We have to set their values. I prefer to define these variables in the assumptions’ sheet, so there is only one place where we have all the variables. I will insert it just below the owners’ capital because the are closely related. We insert few rows by hitting the control and plus (+) buttons togother, and define the titles. We need the loan amount, the interest rate, and the tenure or number of periods. Let us assume the following input variables of the payment function: the loan original amount is 20,000. The interest rate is 7%, and the loan will be paid in five years with yearly payments at year end. We will also redefine the reference with easy names, so it can be used across the workbook without the need to navigate between sheets. keep in mind that the loan amount is based on the funding required for starting up the project and not the opposite. Thus, we can define the capital requirement or structure as a whole, and then decide on the proportion of the loan and investors’ capital. Although this is not required in this class, you can refer to the example feasibility study uploaded to Moodle to see how these can lead to more dynamic. We go back and continue with the amortization schedule. First, we select the calculations’ sheet and go to the bottom. We start by defining the titles. As you notice, for consistency, we have year zero included where it wasn’t the case with assignment two because as explained, it is not required and doesn’t add value. Unlike in class, we will construct the table horizontally so it is easier for you to get the values into the financial statements later on, when we integrate the loan table with them. After the titles, we start with the loan amount which is the remaining balance at the start of the year, then, the payment, followed by the interest charged, repayment of principal, and the remaining balance. We have the structure of the table, and we need to add the required calculations. The loan amount is equal to the remaining of principle from the previous period. For the first year, it is equal to the original amount. For the second year, it is equal to the remaining balance of year one. We just copy the formula into the following years. We can calculate the payment choosing the formula we saw before, or use the built-in Excel function which is the same thing. Anyways, in the last session, we will see how these built-in functions are simply made. I will use the standard equation to calculate the payment, but it is your choice. If you want to use the buil-in function, you use Excel’s PMT() function as we also did in class. Note that here, we didn’t divide the interest rate by 12 as in months, 4 is in quarters, or 2 as in semiannual analysis, because we have the yearly interest rate and the payment is calculated on yearly basis. If it was the same monthly case as in the example we had before, we would have to divide r, the interest rate, by 12 and multiply n, the number of periods, by 12 also. The payment is the same and doesn’t change over time. We just copy the formula to the right, because we redefined the cells references so they are used as absolute reference. The interest is equal to what remains of the loan at the beginning of the year multiplied by the interest rate. The repayment of principal is equal to the payment amount minus the interest charged. Finally, the remaining principle is equal to the loan amount at the beginning of the year minus the repayment of principal. That is it and we are done. Note that we could have avoided constructing this table, and created instead, a smaller table with two rows that are required for the financial statements, but to avoid complications, we create the schedule as we did. Last step is to integrate this table with our financial statements. First, the interest expense is deducted from the net income. So, we have to add it in the income statement. We change two terms. EBDT becomes EBDIT, which stands for earnings before depreciation, interest, and taxes. We also chang EBT to the EBIT, which stands for earnings before interest and taxes. Then, we add three rows below EBIT, and add interest expense, and EBT, which stands for earnings before taxes. We refer the interest expense to its values from the calculations’ sheet. EBT is calculated as EBIT minus the interest expense. We change the tax formula to reflect the new changes. As you notice, the tax amounts get lower, and that is the advantage of leveraging or including debt in capital structure. The net income is calculated the same way as before: EBT minus taxes. That is it for the income statement. Now, we move into the balance sheet. The loan is a liability, and so, we have to add it under liabilities. We insert three rows below total current liabilities, we add the loan. We also total liabilities because our liabilities now don’t only include current ones. We can add titles of current and long-term liabilities above the items like we did for assets, but since we only have one item in each category, and in order to keep table short, we don’t add them because after all we are not following any particular financial reporting standards. In year zero, we acquired the loan. Thus, we insert the amount. Our financial statements are reported at the year end; thus, we have to use the remaining balance at the year end. Total liabilities are equal to current liabilities plus the loan which is long-term liability. We update the formula for total liabilities and owner’s equity to reflect the change in the structure. As you notice, the balance sheet doesn’t close except for the last year where no loan is remaining. Let us move to the last part and adjust the cash flow statement. We have to add the cash flow from financing activities since we have a loan now. Since we only have one loan, we will not create separate fields for loans received and loans’ repayments. If we had more than one loan or plan to obtain more loans in the following years, it would have been better to create separate fields. In our case we will create just one item and call it “Cash from Financing Activities”. In year zero, we receive the amount so we insert it. The interest expense is already deducted from the net income in the income statement. The other cash outflow is the repayment of principal. We insert it as a negative value by adding the “-” sign since it is cash outflow. We could have alternatively adjusted the formula of the cash at the end of the year. The total of interest expense plus repayment of principal is the amount of payment itself. We change the formula of cash at the end of the year to reflect the new additions. We move back to the Balance Sheet to see if it is closing. All is good. The addition of the loan to our study doesn’t affect the other parts of the study, and particularly, the Investment Cashflow Statement, because the statement looks at the cash flow resulting from operations or operating activities, whereas the loan is a financing activity. The idea of the investment cash flow statement is to look at the actual feasibility of a project, to see whether a project is feasible without looking at the sources of finance. In this tutorial, we added a loan to our study. We could similarly change the structure as required. If we have a look at the NPV & IRR, we notice that they have changed due to the fact that after adding the loan, we have more cash at the startup year. Last, I hope that this course was useful to you.