Page Comparison

You can use a Manual Entry driver tab to manage your loan data and calculate the loan repayment amount and the loan interest amount 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 shows an example of how to do this. Brief steps are outlined below.

The example uses the following data:

• Loan Amount: $1,000,000

• Repayment Term: 10 Years (120 Months)

• Interest Rate: 6.25%

• Start Month: December

1. Add the Balance Sheet budget and enter the Loan Amount in the applicable month in the Long-Term Loan section.

   Image RemovedImage Added

2. Add a Manual Entry driver tab (in this example, the tab is called Loan Examples) and add rows for the loan data and to calculate the monthly payment, and interest and principal that will be repaid. See the tab setup below.

   

3. Back in the Balance Sheet budget, reference the data in the Manual Entry tab:

   1. In the Long-Term Loan section, use a formula to reference the Loan Repayment (-Principal).

      

   2. In the Bank section, use formulas to reference the Loan Amount ($1,000,000), the Repayment of Principal (-Principal) and Repayment of Interest (-Interest).

      

4. In the Profit and Loss budget (Main tab), in the Interest section, use a formula to enter the Interest Paid on Loan (-Interest).

   

5. Run scenarios as required:

   • In the Balance Sheet, change the Loan Amount and see how that impacts the Loan Repayment, Repayment of Principal and Repayment of Interest.

   • In the Manual Entry tab, change the loan assumptions, such as the Total Months or Interest Rate, and see how the impacts flow through the budget.

     

Manual Entry tab setup