Results 1 to 6 of 6

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

  1. #1

    Join Date
    Feb 2012
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  2. #2
    Board Regular
    Join Date
    Feb 2013
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  3. #3

    Join Date
    Feb 2012
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!

  4. #4
    Board Regular
    Join Date
    Feb 2013
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,726
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default 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


  6. #6

    Join Date
    Feb 2012
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating a Loan Constant

    That is great! Perfect! Thank You!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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