ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Education and Science»
  • Economics

How to Calculate Loan Amount in Excel

Updated on April 25, 2016

How much you can lend

You are often in a dilemma of how much money you can lend. You know what kind of income you have converted in your obligation and figure out how much of the loan could you still be repaid. Find out what kind of the interest rate is possible to get for a loan in the bank.

You have data on the amount of monthly installment, the interest rate and the loan repayment period. From these data you can calculate how much loan could you get? YES. Below you can see how this can be. You can choose a variety of annuities, different interest rates and different repayment periods. In this way, you will come to that loan that suits you best.

The calculation will be made ​​to compound and the sinple (proportional) method. At the bank ask which method they used. The difference in small amounts and low interest rate is not high. More accurate is compound way, but because of a more simple calculation is using more sinple. In sinple interest is distributed evenly over the entire period, the compound is increasing over the period, as the basis for the calculation take the interest accrued in the previous period. Compound calculation is slightly more favorable to the borrower. You'll find yourself when you prepare the calculation presented here.

Let's start and much success!

Make a template for calculate loan amount

You will use Excel. Do the template as you see it in the table below. Use the same rows and columns, so that you can copy the formula.

Enter the data

Enter all the details before you enter the formula

The repayment period in months

Information is necessary for control previously entered data.

In A7 copy =A5*B5

Loan conformably - compound

The formulas for calculating the amount of credit is very complex, so it is best for you to copy the formulas.

In A9 copy =A3/(1/((((1+(100*(((1+B3/100)^(B5/12))-1))/100)^A5)-1)/(((1+(100*(((1+B3/100)^(B5/12))-1))/100)^A5)*((1+(100*(((1+B3/100)^(B5/12))-1))/100)-1))))

Loan proportional - simple

In B9 copy =A3/((((1+(B3/(1200/B5)))^A5)*((1+(B3/(1200/B5)))-1))/(((1+(B3/(1200/B5)))^A5)-1))

You are finished!

Now you are done with the template. You can use it with data that are of interest for you.

Estimated time of preparation and production

Prep time: 5 min
Cook time: 25 min
Ready in: 30 min
Yields: Calculator loans

How satisfied are you with your financial situation?

5 stars from 1 rating of your financial situation

Methods of clearing

In which the method of clearing the loan is cheaper?

See results

Some examples of the calculation.

Annuity
Annual Interest Rate
Number of annuities
how much monthly installment
Loan compound
Loan simple
50
4
120
1
4955.13
4938.51
100
4
120
1
9910.25
9877.02
500
4
120
1
49551.26
49385.09
1000
4
120
1
99102.51
98770.17
50
4
180
1
6792.47
6759.61
100
4
180
1
13584.94
13519.21
500
4
180
1
67924.69
67596.07
1000
4
180
1
135849.37
135192.15
50
4
240
1
8302.63
8251.09
100
4
240
1
16605.26
16502.19
500
4
240
1
83026.31
82510.93
1000
4
240
1
166052.62
165021.86
50
8
24
1
1108.65
1105.53
100
8
24
1
2217.29
2211.05
500
8
24
1
11086.46
11055.27
1000
8
24
1
22172.91
22110.54
50
8
36
1
1602.17
1595.59
100
8
36
1
3204.33
3191.18
500
8
36
1
16021.67
15955.90
1000
8
36
1
32043.33
31911.81
50
8
60
1
2482.25
2465.92
100
8
60
1
4964.49
4931.84
500
8
60
1
24822.45
24659.22
1000
8
60
1
49644.90
49318.43

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.