Calculating a Loan Constant

[tywalker2276]

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

 

[Sancdar]

This site should help you out: How to Calculate the Loan Constant (Cost of Capital)
Try to implement that in Excel on your own first, and if something goes wrong I'll be happy to help with that.

 

[tywalker2276]

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]

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]

Excel Workbook
	A	B	C
1	Principal	4,000,000.00
2	Rate	5.50%
3	Term	30
4		0.06813468	0.06813
5	Annual payments	272,538.72	272,520.00
6	Payment	272,538.72	272,538.72
7	Rounding difference	0.00	18.72
8
5b

Excel 2003

Cell Formulas
Range	Formula
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]

That is great! Perfect! Thank You!