# Calculating a Loan Constant

#### tywalker2276

##### New Member
Hello all!

I was wondering if someone could help me derive a formula to determine a loan constant. My interest rate is 3.5% (Cell H55) and my Amortization period is 300 months (Cell H56). Any help you can give me would be much appreciated!

Thank you!

Tyler

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### tywalker2276

##### New Member
Using the first example from the suggest website I created the following equation. However I get a different answer than what the website shows. I am not sure where the error is in my formula? Could you please take a look and let me know what did wrong?

=(0.055/12)/(1-(1/(POWER(1+(0.055/12),360))))

Thanks!

#### Sancdar

##### Board Regular
Welp, serves me right for just using the first thing that came up on Google. Looking into it more I'm getting multiple definitions for what a loan constant is. What is your definition?

#### Dave Patton

##### Well-known Member
Excel Workbook
ABC
1Principal4,000,000.00
2Rate5.50%
3Term30
40.068134680.06813
5Annual payments272,538.72272,520.00
6Payment272,538.72272,538.72
7Rounding difference0.0018.72
8
5b
Excel 2003
Cell Formulas
RangeFormula
B4=(B2/12)/(1-(1/(1+B2/12)^(B3*12)))*12
B5=\$B\$1*B4
B6=PMT(B2/12,B3*12,-B1)*12
B7=B6-B5
C4=ROUND((0.055/12)/(1-(1/(1+0.055/12)^360))*12,5)
C5=\$B\$1*C4
C6=PMT(B2/12,B3*12,-B1)*12
C7=C6-C5

#### tywalker2276

##### New Member
That is great! Perfect! Thank You!