Calculating a Loan Constant

tywalker2276

New Member
Joined
Feb 2, 2012
Messages
10
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
 

Some videos you may like

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
Joined
Feb 2, 2012
Messages
10
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
Joined
Feb 6, 2013
Messages
95
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
Joined
Feb 15, 2002
Messages
4,037
Office Version
2010
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,576
Messages
5,469,490
Members
406,656
Latest member
Kriscrawford76

This Week's Hot Topics

Top