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

1. ## 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. ## 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" <LD@discussions.microsoft.com> wrote in message
news:12CC2F25-3BF8-40FB-8B35-AF3938D265C0@microsoft.com...
> 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. ## 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" <LD@discussions.microsoft.com> wrote in message
> news:12CC2F25-3BF8-40FB-8B35-AF3938D265C0@microsoft.com...
> > 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. ## 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" <LD@discussions.microsoft.com> wrote in message
news:6B2DC06C-4EC1-4615-8684-E2EEB36B58CB@microsoft.com...
> 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" <LD@discussions.microsoft.com> wrote in message
>> news:12CC2F25-3BF8-40FB-8B35-AF3938D265C0@microsoft.com...
>> > 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. ## 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" <LD@discussions.microsoft.com> wrote in message
> news:6B2DC06C-4EC1-4615-8684-E2EEB36B58CB@microsoft.com...
> > 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" <LD@discussions.microsoft.com> wrote in message
> >> news:12CC2F25-3BF8-40FB-8B35-AF3938D265C0@microsoft.com...
> >> > 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. ## 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" <LD@discussions.microsoft.com> wrote in message
news:803532F0-8F61-4136-8B6C-8ACA001E33A7@microsoft.com...
> 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" <LD@discussions.microsoft.com> wrote in message
>> news:6B2DC06C-4EC1-4615-8684-E2EEB36B58CB@microsoft.com...
>> > 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" <LD@discussions.microsoft.com> wrote in message
>> >> news:12CC2F25-3BF8-40FB-8B35-AF3938D265C0@microsoft.com...
>> >> > 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
>> >>
>> >>
>> >>

>>
>>
>>

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

#### 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