+ Reply to Thread
Results 1 to 6 of 6

How do I create a formula to calculate the average percentage rat

  1. #1
    LD
    Guest

    How do I create a formula to calculate the average percentage rat

    How do i create a formula in Excel to calculate the average percentage rate
    of a 30 year financing period
    For example Loan amount 135,000 montly payment amount 684.03 number of
    payments 360 intrest rate 4.5% and prepaid finance charge of 1770.57. The
    first 74 payments the monthly payment amount will be 742.53 and for the
    remaining payments of 286 the monthly note will be 684.03

  2. #2
    N Harkawat
    Guest

    Re: How do I create a formula to calculate the average percentage rat

    for the first situation the formula is
    =RATE(360,-684.03,135000-1770.57)*12

    For second situation where the PMT changes after 74th month the Rate
    function would not work.

    however if copied from cell A2 thru A74 $742.53 and from A75 thru A361
    $684.03 and on cell a1 ,$-135000
    Then on cell B1 type the following:
    =IRR(A1:A361,3%/12)*12

    That should give you your average rate of 4.74%

    Its cheaper taking the first option of 4.61%





    "LD" <[email protected]> wrote in message
    news:[email protected]...
    > How do i create a formula in Excel to calculate the average percentage
    > rate
    > of a 30 year financing period
    > For example Loan amount 135,000 montly payment amount 684.03 number of
    > payments 360 intrest rate 4.5% and prepaid finance charge of 1770.57. The
    > first 74 payments the monthly payment amount will be 742.53 and for the
    > remaining payments of 286 the monthly note will be 684.03




  3. #3
    LD
    Guest

    Re: How do I create a formula to calculate the average percentage

    When this calculation is put into a program called easylender a mortgage
    program a annual percentage rate of 4.859% appears how do I create a formula
    in excel to match this rate?

    "N Harkawat" wrote:

    > for the first situation the formula is
    > =RATE(360,-684.03,135000-1770.57)*12
    >
    > For second situation where the PMT changes after 74th month the Rate
    > function would not work.
    >
    > however if copied from cell A2 thru A74 $742.53 and from A75 thru A361
    > $684.03 and on cell a1 ,$-135000
    > Then on cell B1 type the following:
    > =IRR(A1:A361,3%/12)*12
    >
    > That should give you your average rate of 4.74%
    >
    > Its cheaper taking the first option of 4.61%
    >
    >
    >
    >
    >
    > "LD" <[email protected]> wrote in message
    > news:[email protected]...
    > > How do i create a formula in Excel to calculate the average percentage
    > > rate
    > > of a 30 year financing period
    > > For example Loan amount 135,000 montly payment amount 684.03 number of
    > > payments 360 intrest rate 4.5% and prepaid finance charge of 1770.57. The
    > > first 74 payments the monthly payment amount will be 742.53 and for the
    > > remaining payments of 286 the monthly note will be 684.03

    >
    >
    >


  4. #4
    N Harkawat
    Guest

    Re: How do I create a formula to calculate the average percentage

    On cell A1 change the value of -135,000 to -133,229.43 (prepaid finance
    charge $1,770.57)
    That should give you 4.859%



    "LD" <[email protected]> wrote in message
    news:[email protected]...
    > When this calculation is put into a program called easylender a mortgage
    > program a annual percentage rate of 4.859% appears how do I create a
    > formula
    > in excel to match this rate?
    >
    > "N Harkawat" wrote:
    >
    >> for the first situation the formula is
    >> =RATE(360,-684.03,135000-1770.57)*12
    >>
    >> For second situation where the PMT changes after 74th month the Rate
    >> function would not work.
    >>
    >> however if copied from cell A2 thru A74 $742.53 and from A75 thru A361
    >> $684.03 and on cell a1 ,$-135000
    >> Then on cell B1 type the following:
    >> =IRR(A1:A361,3%/12)*12
    >>
    >> That should give you your average rate of 4.74%
    >>
    >> Its cheaper taking the first option of 4.61%
    >>
    >>
    >>
    >>
    >>
    >> "LD" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > How do i create a formula in Excel to calculate the average percentage
    >> > rate
    >> > of a 30 year financing period
    >> > For example Loan amount 135,000 montly payment amount 684.03 number of
    >> > payments 360 intrest rate 4.5% and prepaid finance charge of 1770.57.
    >> > The
    >> > first 74 payments the monthly payment amount will be 742.53 and for the
    >> > remaining payments of 286 the monthly note will be 684.03

    >>
    >>
    >>




  5. #5
    LD
    Guest

    Re: How do I create a formula to calculate the average percentage

    when entering this formula the result is #num! what am I doing wrong?

    "N Harkawat" wrote:

    > On cell A1 change the value of -135,000 to -133,229.43 (prepaid finance
    > charge $1,770.57)
    > That should give you 4.859%
    >
    >
    >
    > "LD" <[email protected]> wrote in message
    > news:[email protected]...
    > > When this calculation is put into a program called easylender a mortgage
    > > program a annual percentage rate of 4.859% appears how do I create a
    > > formula
    > > in excel to match this rate?
    > >
    > > "N Harkawat" wrote:
    > >
    > >> for the first situation the formula is
    > >> =RATE(360,-684.03,135000-1770.57)*12
    > >>
    > >> For second situation where the PMT changes after 74th month the Rate
    > >> function would not work.
    > >>
    > >> however if copied from cell A2 thru A74 $742.53 and from A75 thru A361
    > >> $684.03 and on cell a1 ,$-135000
    > >> Then on cell B1 type the following:
    > >> =IRR(A1:A361,3%/12)*12
    > >>
    > >> That should give you your average rate of 4.74%
    > >>
    > >> Its cheaper taking the first option of 4.61%
    > >>
    > >>
    > >>
    > >>
    > >>
    > >> "LD" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > How do i create a formula in Excel to calculate the average percentage
    > >> > rate
    > >> > of a 30 year financing period
    > >> > For example Loan amount 135,000 montly payment amount 684.03 number of
    > >> > payments 360 intrest rate 4.5% and prepaid finance charge of 1770.57.
    > >> > The
    > >> > first 74 payments the monthly payment amount will be 742.53 and for the
    > >> > remaining payments of 286 the monthly note will be 684.03
    > >>
    > >>
    > >>

    >
    >
    >


  6. #6
    N Harkawat
    Guest

    Re: How do I create a formula to calculate the average percentage

    Are you entering on cell a1 number as negative 133229.43 and the rest from
    A2 thru A361 as positive
    because I sure do get the correct result
    "LD" <[email protected]> wrote in message
    news:[email protected]...
    > when entering this formula the result is #num! what am I doing wrong?
    >
    > "N Harkawat" wrote:
    >
    >> On cell A1 change the value of -135,000 to -133,229.43 (prepaid finance
    >> charge $1,770.57)
    >> That should give you 4.859%
    >>
    >>
    >>
    >> "LD" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > When this calculation is put into a program called easylender a
    >> > mortgage
    >> > program a annual percentage rate of 4.859% appears how do I create a
    >> > formula
    >> > in excel to match this rate?
    >> >
    >> > "N Harkawat" wrote:
    >> >
    >> >> for the first situation the formula is
    >> >> =RATE(360,-684.03,135000-1770.57)*12
    >> >>
    >> >> For second situation where the PMT changes after 74th month the Rate
    >> >> function would not work.
    >> >>
    >> >> however if copied from cell A2 thru A74 $742.53 and from A75 thru A361
    >> >> $684.03 and on cell a1 ,$-135000
    >> >> Then on cell B1 type the following:
    >> >> =IRR(A1:A361,3%/12)*12
    >> >>
    >> >> That should give you your average rate of 4.74%
    >> >>
    >> >> Its cheaper taking the first option of 4.61%
    >> >>
    >> >>
    >> >>
    >> >>
    >> >>
    >> >> "LD" <[email protected]> wrote in message
    >> >> news:[email protected]...
    >> >> > How do i create a formula in Excel to calculate the average
    >> >> > percentage
    >> >> > rate
    >> >> > of a 30 year financing period
    >> >> > For example Loan amount 135,000 montly payment amount 684.03 number
    >> >> > of
    >> >> > payments 360 intrest rate 4.5% and prepaid finance charge of
    >> >> > 1770.57.
    >> >> > The
    >> >> > first 74 payments the monthly payment amount will be 742.53 and for
    >> >> > the
    >> >> > remaining payments of 286 the monthly note will be 684.03
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




+ 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