Loan Amortization

Report of the pertinent information in a short summary about the borrowed $15,000 loan at 14 percent interest rate per annum to be repaid over three year. The loan taken by Messineo LLC amounts to $15,000 and is to be paid annually at an interest rate of 14% per annum for the next three years.

The PMT function is useful in computing the annual end of year payment (scheduled payments), the PMT is a financial function (in spreadsheet) used to compute constant payments of a loan required per period at a constant interest rate. In the spread sheet it is calculated as follows: 

PMT(rate, nper, pv) where: 

Rate is the interest rate for the loan = 14%

Nper is number of repayments for the loan = 3

Pv is the principle = $15,000

Required payments per annum in excel = PMT(14%, 3, 15000). This will give $6,460.97, which is the amount to be paid per annum for three years to fully repay the loan.

Interest Portion for each of the three years

In order to be able to calculate interest portion for each of the three years in spreadsheet, we use IPMT function. In the spreadsheet it is provided as follows: 
 
IPMT( rate, per, nper, pv) where; 
 
Rate is the interest rate = 14% 
 
Per is the period you want to calculate the interest rate. Can be 1, 2, or 3 for this case. 
 
Nper is number of repayments for the loan = 3
 
Pv is the principle = $15,000

For this case:
 
Year 1 = IPMT (14%, 1, 3, 15000) = $$2,100.00
 
Year 2 = IPMT (14%, 2, 3, 15000) = $1,489.46
 
Year 3 = IPMT (14%, 3, 3, 15000 = $793.4

Loan amortization schedule

The following figure sets out the loan amortization schedule of $15,000 loan to be repaid for the next three equal annual installments at the rate of 14%. Details are as provided in the spreadsheet and the appendix. 
 
 Year
 
 Beginning Balance
 
 Interest
 
 Principal 
 
 Scheduled Payment
 
 Ending Balance
 
 1
 
  $ 15,000.00 
 
  $ 2,100.00 
 
  $ 4,360.97 
 
  $ 6,460.97 
 
  $ 10,639.03 
 
 2
 
  $ 10,639.03 
 
  $ 1,489.46 
 
  $ 4,971.51 
 
  $ 6,460.97 
 
  $ 5,667.52 
 
 3
 
  $ 5,667.52 
 
  $793.46 
 
  $ 5,667.53 
 
  $ 6,460.98 
 
  $ 0.00 
 
 Total 
 
  $4,382.92 
 
  $ 15,000.00 
 
  $ 19,382.91

Executive Summary

The $ 15,000 loan is offered on reducing balance basis, this means interest is calculated from the principal balance and not original balance. For instance, in Messineo LLC case the company received $15,000 as loan at an interest rate of 14% on reducing balance to be repaid in three installments annually. The first and subsequent years’ interests presented in the amortization schedule is calculated and described as follows:

Year 1
 
End of year 1 interest = Principal X rate of interest = $15,000 X 14% = $2,100
 
End of year 1 cumulative loan balance before payment = Principal + Interest = $15,000 + $2,100 = $17,100
 
But the company makes scheduled payments of $6,460.97 annually (as presented by use of PMT function calculation above). Therefore,
 
Principal balance after scheduled payment = $17,100 - $6,460.97 = $ 10,639.03 which is the beginning principal balance of loan for year 2.
 
Year 2
 
The beginning principal balance in year 2 = $ 10,639.03.
 
End of year 2 interest = Principal Balance at the beginning of year 2 X rate of interest = $ 10,639.03 X 14% = $ 1,489.46
 
Explanation: the interest payable at the end of year two is relatively lower than that of year one, this can be explained by a relatively lower beginning principal in year 2 after scheduled payment was made, while the interest rate remained the same at 14%. 
 
End of year 2 cumulative loan balance before scheduled payment =  Principal Balance at the beginning of year + Interest = $ 10,639.03 + $ 1,489.46 = $ 12,128.49 
 
End of year 2 loan balance after scheduled payment = $ 12,128.49 - $6,460.97 = $ 5,667.52. This will be the opening balance in year 3. 
 
Year 3

The beginning principal in year 3 = $ 5,667.52 i.e. the closing principal in year 2 after schedule payment. 

End of year 3 interest computation = Principal Balance at the beginning of year 3 X rate of interest = $ 5,667.52 X 14% = $793.46 

The interest is lower compared to year two since the interest is computed from the opening loan balance (which is relatively lower after scheduled payment) for the period (year 3) at the same rate (14%); the rate remains constant throughout the loan tenure. 

Cumulative loan balance at the end of year 3 before scheduled payment will be = $ 5,667.52 + $793.46 = $6,460.97 
 
Cumulative loan balance at the end of year 3 after scheduled payment will be = $6,460.97 - $6,460.97 = $ 0.00 
 
The third instalment will clear the loan. 
 
Total interest paid for the three years =$ 2,100.00 (year 1) + $ 1,489.46 (year 2) + 793.45 (year 3) = $ 4,382.92 
 
Total Principal paid for the three years =$ 4,360.97 + $ 4,971.51 + 5,667.53 = $ 15,000
 
Total payment by Messineo LLC will be: Total Principal + Interest = $ 15,000 + $ 4,382.92 =$ 19,382.91

Taxation and Interest Rates

Interest on loan is an allowed expense and therefore deductible from the operating profit computed at the end of the year. This means, the interest reduces the income tax payable at the end of the financial year. In Messineo LLC case, without considering the time value of money, suppose

EBIT1, EBIT2, and EBIT3, represent the Earnings Before Interest and Tax (EBIT) for years 1, 2, and 3 respectively, and that the rate of taxation is RT, then: 
 
Total tax for 3 years if Messineo LLC borrows $15,000 and repays within the period will be: 
 
(EBIT1 + EBIT2, + EBIT3 - $ 4,382.92)RT = M
 
If Messineo LLC does not borrow, tax will be
 
(EBIT1 + EBIT2, + EBIT3 ) RT = N
 
M – N = - ($ 4,382.92)RT 
 
This means the company will pay less tax by ($ 4,382.92)RT if it borrows $ 15,000 at the rate of 14% for 3 years tenure.

References

Management Accounting and Excel. (2014). Advanced Excel Reporting for Management Accountants, 3-7.

Parasotskaya, N. (2014). Accounting of loans and borrowings. Auditor Аудитор, 0(22), 79-85.

Rackliffe, U. R., & Ragland, L. (2016). Excel in the accounting curriculum: perceptions from accounting professors. Accounting Education, 25(2), 139-166

Appendix

Detailed Loan amortization schedule

Year

Beginning Balance

Interest Rate

Interest

Principal 

Scheduled Payment
 
Balance before Scheduled payments
 
Ending Balance

1
$15,000.00
14%
$2,100.00
$4,360.97
$6,460.97
$17,100.00
$10,639.03

2
$ 10,639.03
14%
$1,489.46
$4,971.51
$6,460.97
$12,128.49
$5,667.52

3
$5,667.52
14%
$793.45
$5,667.53
$6,460.98

$6,460.98
$(0.00)

Total

$4,382.92
$15,000.00
$19,382.92

Total Interest paid for the loan $4,382.92

Total Payments by Messineo LLC towards the loan at the end of the period $19,382.92