+4 votes
41 views
Fixed Amount Loan Repayment Table Excel | Credit table

in Office by (303k points)
reopened | 41 views

1 Answer

+5 votes
Best answer

Microsoft Excel is one of the most complete options to work with data in a much more centralized way of each one of them. Excel has hundreds of functions and formulas designed for a much more complete work and one of them is to use formulas to determine the amortization table of a loan, remember that amortization consists of gradually reducing a debt or a loan with periodic payments, the amortization table in Excel will give us a global detail of each of the payments until we pay in full said credit..

 

Amortization table requirements
To correctly create the amortization table in Excel it is necessary to have the following:

 

  • Amount of the credit: we must know exactly the amount of the loan, this is the value that the bank finally gives us.
  • Interest rate: knowing the interest rate charged by the financial institution is a key aspect, as a general rule the interest rate is deducted annually.
  • Number of payments: knowing the number of payments allows us to know how long we will be linked to the financial entity, these payments are represented in monthly periods such as 12, 24, 36, 48, etc.

 

 

 

Data
In our case we handle the following data:

 

  • Loan Amount: $ 380,000
  • Months: 12
  • Monthly interest: 3%

 

 

 

 

image

 

 


With these data we are going to see how to make an amortization table Excel fixed fee in detail in text and video.

 

To stay up to date, remember to subscribe to our YouTube channel!   SUBSCRIBE

 

 


1. Calculate Excel fixed fee

 

Step 1

The first step to take will be to calculate the fixed fee that we must pay, for this we go to the cell "Fixed fee per month" and we will use the formula "Payment" as follows:
 = PAYMENT (B4; B5; B3) 
Step 2

The following values ​​have been defined in it:
 (monthly interest; number of months; loan amount) 
Step 3

The default result will give a negative number:

 

 

 

 

image

 

Step 4

For the amortization table to work correctly, we must convert the negative value to positive, for this we enter a sign - before the "payment" function:
 = -PAYMENT (B4; B5; B3) 
Step 5

Now the number will be positive:

 

 

 

 

 

image

 

 


2. Set up depreciation table Excel

 

 

Step 1

It is time to manage the table to know in detail the values ​​to be paid and collected, the first thing will be to enter the months, as in this case they are 12, we enter from period 0 to 12 (in period zero we enter the disbursement of the credit).

 

In the Final Balance cell we can reference the cell where the loan amount has been entered:

 

image

 

 

Step 2

Now, we reference cell F10, which is where we have entered the loan amount, into cell B11 associated with the initial balance of the loan:

 

image

 

 

Step 3

In the section "Fixed fee" we have already defined the monthly fixed value, this value will not change throughout this section, in cell C11 we enter the reference of the cell where the fixed fee has been defined (B6), we must convert this cell in absolute reference so that its value does not change as we drag the cell down, when entering the reference we press the F4 key and we will see the $ sign next to the column and the cell:
= $ B $ 6

 

image

 

 

Step 4

Now we calculate the monthly interest, for this we multiply the initial balance by the interest rate charged with the following formula in this example:
 = B11 * $ B $ 4 
Note
cell B4 has been left absolute because it is a fixed value.

 

 

 

 

image

 

 

Step 5

Now it is time to calculate the capital paid which is the difference between the fixed installment minus the monthly interest paid, we will use the following formula:
 = C11-D11 
Step 6

The result of this is what will be reduced from the initial debt:

 

 

 

 

image

 

 

Step 7

To define the final balance we will take the initial balance minus the paid-in capital:
 = B11-E11 
image

 

 

Step 8

Finally, we simply select the cells of the amortization table and drag them from the lower right corner to the last defined period so that the values ​​to be paid are displayed:

 

image

 

 

With this simple method we can create an amortization table as an example in Excel and with it, take precise control over the values ​​that we must pay month by month and know with certainty whether or not the agreed conditions are met.

 


by (1.9m points)

Related questions

+5 votes
1 answer
asked Sep 19, 2019 in Office by backtothefuture (303k points) | 26 views
+3 votes
1 answer
asked May 24 in Help by backtothefuture (303k points) | 33 views
+5 votes
1 answer
+3 votes
1 answer
asked Oct 8, 2019 in Office by backtothefuture (303k points) | 72 views
+5 votes
1 answer
Please leave a comment about whether the solution works or not (with device model)   [X]Close
5,793 questions
5,883 answers
163 comments
2 users