Calculate and budget loan repayments

Manage your loan data in a Manual Entry tab and calculate repayment and interest amounts per period.

User permissions: Budgets & Forecasts and Manage Budgets & Forecasts

You can use a Manual Entry tab to manage your loan data and calculate the loan repayment and interest amounts per period. You can then use that data as the basis of a loan financing mini-driver for connecting the Profit and Loss and Balance Sheet.

The video below shows an example of how to do this. The example uses the following data:

• Loan Amount: $1,000,000

• Repayment Term: 10 Years (120 Months)

• Interest Rate: 6.25%

• Start Month: December

The Manual Entry tab setup is as follows:

Row	Action	Details
Interest Rate	Enter the interest rate	6.25% per month
Remaining Loan Amount	Use a formula to reference to the Loan Amount on the Balance Sheet tab	($1,000,000 in the first year, starting in December)
Interest	Use a formula to calculate the interest	= Remaining Loan Amount * ((Interest Rate / 100) / 12)
Monthly Payment Amount Total	Use this logic to calculate the monthly payment: = (P * r * (1 + r)^n) / ((1 + r)^n - 1) Where: P is the loan amount (principal) r is the monthly interest rate (annual interest rate divided by 12) n is the total number of payment periods (number of years multiplied by 12)	= (1000000 * 0.0625 / 12 * (1 + 0.0625 / 12)^120) / ((1 + 0.0625/12)^120 – 1)
Principal	Use a formula to calculate the principal	= Monthly Repayment Amount Total - Interest