Excel template loan repayment reducing balance
Excel template loan repayment reducing balance
This article provides details of Excel template loan repayment reducing balance that you can download now.
Microsoft Excel software under a Windows environment is required to use this template
These Excel template loan repayment reducing balance work on all versions of Excel since 2007.
Examples of a ready-to-use spreadsheet: Download this table in Excel (.xls) format, and complete it with your specific information.
To be able to use these models correctly, you must first activate the macros at startup.
The file to download presents three templates Excel template loan repayment reducing balance:
- Simple Excel template loan repayment reducing balance with graphic
- Excel template daily loan principal and interest calculation spreadsheet
This spreadsheet shows in daily detail the effect of amounts and timing of fees and loan payments on a fixed rate loan. This sheet can be helpful in understanding the effects of late and early payments and fees. This spreadsheet is intended to be an educational tool. It is not intended to be a basis for financial or other important decisions.
- Excel loan calculation and analysis Template
This comprehensive loan calculation template enables users to analyze loans that are repaid on a monthly basis and subject to daily interest calculations. The template has been designed specifically for the analysis of loans that are subject to variable interest rates and also accommodates ad hoc loan repayments that fall outside the scope of standard amortization tables. Interest calculations are based on daily loan account balances (same calculation basis as used by most financial institutions) and an unlimited number of interest rate changes can be recorded. Loan account transactions need to be recorded by the user and a comprehensive loan account statement is automatically calculated. Loan repayment forecasts are calculated for the remaining loan repayment period in order to create an overall loan account analysis over the entire loan period.
Amortized Loans
- When a loan is an amortized loan, each payment is understood to consist of:
- the interest due on the outstanding loan balance;
- the rest of the payment which goes towards reducing the outstanding loan balance and which is referred to as the principal payment.
- The chart (table) containing the payment amount, interest paid in each payment, principal repaid in each payment and the outstanding balance after each payment is called the amortization schedule
The Sinking Fund Method
- We assume that the payments made prior to the end of the loan term do not contain any portion of the principal, i.e., they only go toward the interest
- Hence, a single “lump-sum” payment should repay the entire loan at the end of the loan term.
- In order to finance this final payment, the borrower might wish to make deposits on a separate savings account during the life of the loan. This account is called the sinking fund account.
- This repayment method is referred to as the sinking fund method
- Note that we need to differentiate between two accounts in this repayment schedule, i.e., there are two interest rates at play
- We usually denote the interest rate governing the loan by i, and the interest rate of the sinking fund account by j
- It is customary (but not necessary) that we assume that j < i
REDUCING BALANCE LOANS
The Reducing Balance Method is mainly used to calculate the total interest for housing or mortgage property loans wherein the interest to be paid by the customer is calculated based on the outstanding loan amount after periodic repayments. Being the preferred option compared to the Fixed Interest Rate, Reducing Balance Rate or the Diminishing Rate is used to calculate the interest amount for overdraft facilities and credit cards as well. This method is beneficial to the customers since they have to pay less amount of interest as the loan tenure progresses considering that the interest is calculated based on the outstanding principal loan amount. In this method, the interest decreases after each monthly installment is paid since the remaining balance becomes lesser than the previous month with the payment of each EMI. The depreciation rate percentage is applied on reducing balance of asset.
The formula for the Reducing Balance Method can be represented as,
Amount of interest for each installment = Applicable rate of interest * Remaining loan amount
Advantages of the Reducing Balance Method
There are numerous significant advantages to the Reducing Balance Method. Learn about the advantages of the
- This method is simple to implement and easy to understand.
- For businesses, the amount which is charged to the profit & loss account towards depreciation and repairs remains somewhat uniform as the period of the loan progresses.
- The Reducing Balance Method is acceptable for income tax purposes. The depreciation tax deduction can be claimed in a larger amount.
- This method does match the cost and revenue of any business. The greater amount of depreciation during the initial years which is matched against the higher amount of revenue that is generated by the increased production by using a new asset.