# How to Create a Line of Credit Payment Calculator in Excel

## Introduction to Line of Credit Payment

A line of credit is a flexible loan from a bank or financial institution. It allows you to borrow a predetermined amount of money as needed and repay it either immediately or over time. When you borrow from a line of credit, interest is added by the lender. Borrowers often use lines of credit for projects with unpredictable costs or to bridge gaps in irregular monthly income.

The equation for calculating the line of credit payment is as follows:

[ ∑ {(A x N) / n} + O ] x i

Where,

• A represents the total cost of all purchases made during the billing cycle.
• N represents the number of billing cycles that have passed since the purchase date.
• n represents the billing period length.
• O represents possible opening balance.
• i represents the interest rate.

## Dataset Overview

We’ll use the following data set to demonstrate the procedure.

### Step 1 – Prepare Data Set with Available Information

• Create a list of details for all purchases made during the period from July 1 to July 31.
• Include the purchase date and amount for each transaction.

### Step 2 – Determine Average Daily Balance

• Calculate the remaining days after each purchase using the DAYS function in cell E5:
`=DAYS(\$B\$10,B5)`

• Press Enter to see the result, and drag the formula down to fill the remaining cells in column E.

• Calculate the average balance after the first purchase in cell F5:
`=(D5*E5)/31`

• Press Enter and the formula down to fill the remaining cells in column F.

• Measure the total average balance using the SUM function in cell F11:
`=SUM(F5:F10)`

• Press Enter.

### Step 3 – Calculate Monthly Interest Rate

• Convert the yearly interest rate (16%) to a monthly rate for July 21:
`=16%/ 365*31`

• Press Enter.
• This results in a monthly interest rate of 36%.

### Step 4 – Show Final Result

• Arrange all required data in the same worksheet.
• In cell E15, calculate the line of credit payment:
`=(F11+F13)*C13`

• Press Enter to see the result.
• The calculated line of credit payment for the given data and information is \$311.58.

## Things to Remember

• Input percentages correctly to avoid incorrect results.
• Exclude empty cell references when calculating the total average balance.

## Related Articles

<< Go Back to Payment Calculator | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF