# Thread: Calculating a Loan Constant Thanks: 0 Likes: 0

1. ## Calculating a Loan Constant

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  Reply With Quote

2. ## Re: Calculating a Loan Constant

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.  Reply With Quote

3. ## Re: Calculating a Loan Constant

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!  Reply With Quote

4. ## Re: Calculating a Loan Constant

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?  Reply With Quote

5. ## Re: Calculating a Loan Constant

5b
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
Excel 2003

Worksheet Formulas
CellFormula
B4=(B2/12)/(1-(1/(1+B2/12)^(B3*12)))*12
C4=ROUND((0.055/12)/(1-(1/(1+0.055/12)^360))*12,5)
B5=\$B\$1*B4
C5=\$B\$1*C4
B6=PMT(B2/12,B3*12,-B1)*12
C6=PMT(B2/12,B3*12,-B1)*12
B7=B6-B5
C7=C6-C5  Reply With Quote

6. ## Re: Calculating a Loan Constant

That is great! Perfect! Thank You!  Reply With Quote

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•