In this article, I will show you how to create a line of credit payment calculator in Excel. I will demonstrate the whole procedure using the Microsoft 365 version. However, you can use any version of Microsoft Excel at your convenience and follow this tutorial.

**Table of Contents**hide

## Download Practice Workbook

You can download the free **Excel** workbook here and practice on your own.

## Introduction to Line of Credit Payment

A line of credit is a versatile loan taken from a bank or a financial institution that consists of a set sum of money that you can borrow as needed and repay either instantly or over time. As soon as someone borrows money on a line of credit, lenders add interest to it. Most frequently, borrowers use lines of credit to finance projects whose costs they cannot foresee in advance or fill in the gaps for projects that have an irregular monthly income. The equation for calculating line of credit payment is,

**[ ∑ {(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 periods length
- O represents possible opening balance
- i represents the interest rate

## 4 Easy Steps to Create Line of Credit Payment Calculator in Excel

In this article, you will see four easy steps to make a line of credit payment calculator in Excel. In the first step, I will prepare the data set with the available information. Then, I will demonstrate the process of determining an average daily balance in the second step. Thirdly, you will see the calculation of the monthly interest rate in the third step. And finally, I will present the final result after gathering data from those previous steps.

To demonstrate my further procedure, I will use the following data set.

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

First of all, I have to prepare the data set with all the available information. For doing that,

- Firstly, make a list of the details of all the purchases during the period of July 1 to July 31.
- Here, you can see the details for all the purchases along with their respective date and amount.

### Step 2: Determine Average Daily Balance

In the second step, I will determine the average daily balance from the previous step’s data set. For that, I have to find out the remaining days after each purchase and then calculate the average balance. To do that,

- First of all, insert the following formula of
**the DAYS function**in cell**E5**to calculate the remaining days after completing the first purchase.

`=DAYS($B$10,B5)`

- Secondly, press
**Enter**to see the result, and with the help of**AutoFill**get the values for the lower cells of the column as well.

- Thirdly, to determine the average balance after the first purchase, enter the following formula in cell
**F5**.

`=(D5*E5)/31`

- Fourthly, to see the numerical value from the above formula press
**Enter**. - Then, drag the formula to the lower cells using the
**Fill Handle**.

- Fifthly, to measure the total average balance write the following
**SUM**function formula in cell**F11**.

`=SUM(F5:F10)`

- Finally, after pressing
**Enter**, you will get the desired value of the total average balance from the above formula.

**Read More:** **How to Calculate Monthly Payment in Excel (2 Handy Ways)**

**Similar Readings**

**How to Calculate Auto Loan Payment in Excel (with Easy Steps)****Calculate Car Payment in Excel (with Easy Steps)****Create Progressive Payment Calculator in Excel (with Easy Steps)****How to Create Snowball Payment Calculator in Excel****How to Calculate Coupon Payment in Excel (4 Suitable Examples)**

### Step 3: Calculate Monthly Interest Rate

In the previous discussion, you will see the interest rate for this loan payment but that is a yearly rate. Follow the following steps to calculate the monthly interest rate from the previous information.

- First of all, to calculate the monthly interest rate for July 21, use the following formula in cell
**E13**.

`=16%/ 365*31`

- Secondly, after pressing
**Enter**, you will get the desired monthly interest rate which is**36%**.

**Read More:** **How to Calculate Monthly Payment on a Loan in Excel (2 Ways)**

### Step 4: Showing Final Result

In the last step, I will show you the final result of this procedure, by using the data from the previous steps. For that,

- Firstly, bring all the required data in the same worksheet like the following picture.
- Then, in cell E15, insert the following formula to calculate the line of credit payment.

`=(F11+F13)*C13`

- Finally, to see the result hit the Enter button and get the value of the line of credit payment for the above data and information which is
**$311.58**.

**Read More:** **How to Make a Cash Payment Voucher Format in Excel**

## Things to Remember

- While inserting percentages into the calculation, be careful to input them in the correct format. Otherwise, you will get an incorrect result.
- When you calculate the total average balance, do not take the empty cell reference in the formula.

## Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to create a line of credit payment calculator in Excel. Please share any further queries or recommendations with us in the comments section below.

The** ExcelDemy** team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.

**Related Articles**

**How to Calculate Monthly Payment with APR in Excel****Calculate Loan Payment in Excel (4 Suitable Examples)****How to Calculate Balloon Payment in Excel (2 Easy Methods)****Calculate Down Payment in Excel Using VLOOKUP****How to Calculate Monthly Mortgage Payment in Excel (2 Ways)****Calculate a Lease Payment in Excel (4 Easy Ways)**