Need Help with a Formula

[Guest]

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

 

[JE McGimpsey]

In line:

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