Need Help with a Formula

[busterbrown885]

I am new to excel. Trying to set up a formula for the following 2 examples

1. Semi Annual payments of $440,000 for 20 years, or 8Million dollars now,
what is the interest rate that was used to arrive at the 8 million dollars

2. Semi annual payments of $440,000 for 20 years, an interest rate of 7%,
how much should the payment be in a lump sum today?

Thank you for your help, if you could provide me the formula so that I can
play around with various amounts or interest rates that would be helpful.

[Don]

=RATE(40,-440000,0,8000000)*2 = -9.3945%

=PV(0.07/2,40,440000) = ($9,396,231.83)


Don Pistulka


"busterbrown885" <[email protected]> wrote in message
news:[email protected]...
>I am new to excel. Trying to set up a formula for the following 2 examples
>
> 1. Semi Annual payments of $440,000 for 20 years, or 8Million dollars now,
> what is the interest rate that was used to arrive at the 8 million dollars
>
> 2. Semi annual payments of $440,000 for 20 years, an interest rate of 7%,
> how much should the payment be in a lump sum today?
>
> Thank you for your help, if you could provide me the formula so that I can
> play around with various amounts or interest rates that would be helpful.

[JE McGimpsey]

In line:

In article <[email protected]>,
"busterbrown885" <[email protected]> wrote:

> I am new to excel. Trying to set up a formula for the following 2 examples
>
> 1. Semi Annual payments of $440,000 for 20 years, or 8Million dollars now,
> what is the interest rate that was used to arrive at the 8 million dollars

=RATE(20*2,-440000,8000000,0)*2 ===> 9.17%


> 2. Semi annual payments of $440,000 for 20 years, an interest rate of 7%,
> how much should the payment be in a lump sum today?

=PV(A1/2,40,-440000) ===> $9,396,231.83

[Don]

sorry,

I read the first question wrong. should be:

=RATE(40,-440000,8000000)*2 9.1689%

=PV(0.07/2,40,440000) = ($9,396,231.83)



Don Pistulka


"Don" <[email protected]> wrote in message
news:[email protected]...
> =RATE(40,-440000,0,8000000)*2 = -9.3945%
>
> =PV(0.07/2,40,440000) = ($9,396,231.83)
>
>
> Don Pistulka
>
>
> "busterbrown885" <[email protected]> wrote in
> message news:[email protected]...
>>I am new to excel. Trying to set up a formula for the following 2 examples
>>
>> 1. Semi Annual payments of $440,000 for 20 years, or 8Million dollars
>> now,
>> what is the interest rate that was used to arrive at the 8 million
>> dollars
>>
>> 2. Semi annual payments of $440,000 for 20 years, an interest rate of 7%,
>> how much should the payment be in a lump sum today?
>>
>> Thank you for your help, if you could provide me the formula so that I
>> can
>> play around with various amounts or interest rates that would be helpful.
>
>