+ Reply to Thread
Results 1 to 7 of 7

Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Payments

  1. #1
    Registered User
    Join Date
    01-06-2008
    Posts
    19

    Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Payments

    Hello Friends:

    Please help me with a formula that can tell me how much interest rate (in percentage) I paid on this loan where I have the total interest paid over all monthly payments, original loan amount, and number of monthly payments made. At the end of the term of 360, all interest and principal was paid off.

    Please see attached.
    Attached Files Attached Files

  2. #2
    MoneyMaker
    Guest

    Re: Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Paym

    Quote Originally Posted by gurpreet161 View Post
    Hello Friends:

    Please help me with a formula that can tell me how much interest rate (in percentage) I paid on this loan where I have the total interest paid over all monthly payments, original loan amount, and number of monthly payments made. At the end of the term of 360, all interest and principal was paid off.

    Please see attached.
    Monthly Rate
    =RATE(360,0,195000,-331068.81)
    0.14714%

    Annual Rate
    ==RATE(360,0,195000,-331068.81)*12
    1.7657%
    Last edited by MoneyMaker; 04-21-2013 at 11:06 PM. Reason: Fixed the incorrect money amount

  3. #3
    MoneyMaker
    Guest

    Re: Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Paym

    Quote Originally Posted by gurpreet161 View Post
    Hello Friends:

    Please help me with a formula that can tell me how much interest rate (in percentage) I paid on this loan where I have the total interest paid over all monthly payments, original loan amount, and number of monthly payments made. At the end of the term of 360, all interest and principal was paid off.

    Please see attached.
    I have given you an interest rate in the last reply

    In your original post, you provided us with the loan amount, number of periods and interest paid on loan

    Usually a loan amount, monthly payment and number of periods are known and one has to find the rate

    Since a monthly payment was not given, I had to find the total amount that you repaid which includes the principal and interest

    Now since time value of money equation looks this way

    PV (1+RATE)^NPER + PMT (1+RATE*type)[ {(1+RATE)^NPER} - 1 ]/RATE + FV = 0

    Thus you would need to have the known values for PV (loan amount), NPER (number of periods), PMT (monthly payment) and FV (salvage value) to solve for RATE (periodic rate)

    But since we don't have periodic payment yet we do have the future value of the investment which is the amount you paid back along with the initial amount of investment the amount you borrowed along with number of period. This allows us to rewrite the TVM equation as follows

    PV(1+RATE)^NPER + FV = 0

    Where FV is the future value of periodic monthly payment plus the salvage value

    Salvage value is zero and future value of periodic payment would be the loan amount plus the interest paid on it

    FV = $195,000 + $136,068.81
    FV = $331,068.81

    Putting the values in the formula above leads us to this

    195,000(1+RATE)^360 + (-331,068.81) = 0
    195,000(1+RATE)^360 - 331,068.81 = 0
    195,000(1+RATE)^360 = 331,068.81
    (1+RATE)^360 = 331,068.81/195,000
    (1+RATE) = [ 331,068.81/195,000 ]^(1/360)
    RATE = [ 331,068.81/195,000 ]^(1/360) - 1
    RATE = 1.697788777^(1/360) - 1
    RATE = 1.001471433 - 1
    RATE = 0.001471433
    --ERROR---RATE = 0.471433%
    RATE = 0.1471433%
    Annual RATE = 0.1471433% * 12
    Annual RATE = 1.76572%

    Now we are in a position to find your monthly loan repayment

    RATE = 1.76572%
    NPER = 360
    PV = 195,000

    =PMT (1.76572006%/12, 360, 195000)
    ($698.13)

    That is almost $700 per month for 30 years

    P.S. Was this a homework assignment or did you really have a loan such as this
    Last edited by MoneyMaker; 04-21-2013 at 11:52 PM. Reason: fixed the typo

  4. #4
    Registered User
    Join Date
    01-06-2008
    Posts
    19

    Re: Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Paym

    MoneyMaker, I do not have a monthly payment and that's why so much work to establish the rate. Also, I just want to make sure your calculation is accurate.

    A small error I see is in 0.471433% * 12 to calculate Annual Interest Rate. I show this coming to 5.66%.

    Also, I have been charged close to 5.05% and this is the confirmation I was looking to get. Let me play around more with what you did and see may be there is a calculation mistake. You have been wonderful with some mathematic lesson here and gives me something to work with.

  5. #5
    MoneyMaker
    Guest

    Re: Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Paym

    Quote Originally Posted by gurpreet161 View Post
    MoneyMaker, I do not have a monthly payment and that's why so much work to establish the rate. Also, I just want to make sure your calculation is accurate.

    A small error I see is in 0.471433% * 12 to calculate Annual Interest Rate. I show this coming to 5.66%.

    Also, I have been charged close to 5.05% and this is the confirmation I was looking to get. Let me play around more with what you did and see may be there is a calculation mistake. You have been wonderful with some mathematic lesson here and gives me something to work with.
    I am sorry to have made a error in typing

    This 0.471433% * 12

    is suppose to be this

    0.1471433% * 12

    So the actual annual rate 1.76572% is what I get for your data

  6. #6
    MoneyMaker
    Guest

    Re: Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Paym

    Quote Originally Posted by gurpreet161 View Post
    MoneyMaker, I do not have a monthly payment and that's why so much work to establish the rate. Also, I just want to make sure your calculation is accurate.

    A small error I see is in 0.471433% * 12 to calculate Annual Interest Rate. I show this coming to 5.66%.

    Also, I have been charged close to 5.05% and this is the confirmation I was looking to get. Let me play around more with what you did and see may be there is a calculation mistake. You have been wonderful with some mathematic lesson here and gives me something to work with.
    At an annual interest rate of 5.05% your monthly payment comes out to ($1,052.77)

    EDIT---

    Let me see what is wrong with the way I calculated the interest rate

    Give me a minute
    Last edited by MoneyMaker; 04-22-2013 at 12:10 AM. Reason: editing

  7. #7
    MoneyMaker
    Guest

    Re: Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Paym

    Quote Originally Posted by gurpreet161 View Post
    MoneyMaker, I do not have a monthly payment and that's why so much work to establish the rate. Also, I just want to make sure your calculation is accurate.

    A small error I see is in 0.471433% * 12 to calculate Annual Interest Rate. I show this coming to 5.66%.

    Also, I have been charged close to 5.05% and this is the confirmation I was looking to get. Let me play around more with what you did and see may be there is a calculation mistake. You have been wonderful with some mathematic lesson here and gives me something to work with.
    I am sorry to have performed the rate calculation under wrong assumptions

    Your loan amount is $195,000 and you state that interest paid on this amount is $136,068.81 thus the total amount you repaid is $331,068.81

    The monthly payment would then be calculated by dividing the total amount paid by 360 thus your monthly payment is $919.64


    Loan amount = $195,000
    Interest Paid = $136,068.81
    Total amount repaid = $331,068.81

    Monthly payment = $331,068.81 / 360
    Monthly payment = $919.64

    Now that we have the monthly payment, we have all three items required to calculate the monthly rate

    NPER 360
    PMT $919.64
    PV $195,000
    RATE = (360, -919.64, 195000)
    0.325%

    Annual Rate 3.90%

    If you state that the quoted rate is around 5.05% then something else is wrong with your numbers. As with an annual rate of 5.05% the total interest you pay back on a loan of $195,000 would be

    =CUMIPMT(5.05%/12,360,195000,1,360,0)
    ($183,996.86)

    Where as with an annual rate of 3.9% the total interest paid back is
    =CUMIPMT(3.89894899%/12,360,195000,1,360,0)
    ($136,068.81)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1