Net Present Value Template with Weekly Payments

[carosati3]

I am trying to put together a template showing the net present value of three years' of weekly payments. Each payment is $5,000. I am trying to compare that to lump sum payment at the end of each quarter for the three years and also to a lump sum payment at the end of each of the three years. Any help is greatly appreciated. I have searched all over and am coming up empty. Thanks in advance!!

 

[joeu2004]

I am trying to put together a template showing the net present value of three years' of weekly payments. Each payment is $5,000. I am trying to compare that to lump sum payment at the end of each quarter for the three years and also to a lump sum payment at the end of each of the three years. Any help is greatly appreciated.

It is unclear whether you are talking about a loan or an invest; and if an invest, whether "payment" is a contribution (reinvestment) or a withdrawal.

I interpret this to be an annuity (investment with no regular contributions, and you are trying to determine the initial investment required to support regular withdrawals. In that context, perhaps the following is what you want.

    A            B         C              D
1                Freq/Yr                  Init Invest
2   Annl Rate                     4.00% 
3   Term (yrs)                    3 
4   Final Bal                    $0.00 
5   Wkly W/D     52          $5,000.00    -$734,751.22
6   Qtrly W/D     4         $65,000.00    -$731,580.04
7   Annl W/D      1        $260,000.00    -$721,523.67

Formulas:
C6: =$C$5*$B$5/B6
C7: =$C$5*$B$5/B7
D5: =PV($C$2/B5,B5*$C$3,C5,$C$4)
Copy D5 into D6:D7

[EDIT] If you want to see the initial investment as a positive value, change =PV(...) to =-PV(...).

 

[carosati3]

joeu2004-


thanks for the reply. i apologize for not being clear in the beginning. this would be considered an investment. we are taking over a book of business from one of our customers to invoice the third party customer directly. we would be saving about $5,000 per week in credits by doing this. i am trying to determine what the net preset value of this $5,000 per week would be in three years. i hope that helps. i am lost on how to create the spreadsheet.

thanks in advance!

 

[carosati3]

It is unclear whether you are talking about a loan or an invest; and if an invest, whether "payment" is a contribution (reinvestment) or a withdrawal.

I interpret this to be an annuity (investment with no regular contributions, and you are trying to determine the initial investment required to support regular withdrawals. In that context, perhaps the following is what you want.

    A            B         C              D
1                Freq/Yr                  Init Invest
2   Annl Rate                     4.00% 
3   Term (yrs)                    3 
4   Final Bal                    $0.00 
5   Wkly W/D     52          $5,000.00    -$734,751.22
6   Qtrly W/D     4         $65,000.00    -$731,580.04
7   Annl W/D      1        $260,000.00    -$721,523.67

Formulas:
C6: =$C$5*$B$5/B6
C7: =$C$5*$B$5/B7
D5: =PV($C$2/B5,B5*$C$3,C5,$C$4)
Copy D5 into D6:D7

[EDIT] If you want to see the initial investment as a positive value, change =PV(...) to =-PV(...).

thanks for the reply. i apologize for not being clear in the beginning. this would be considered an investment. we are taking over a book of business from one of our customers to invoice the third party customer directly. we would be saving about $5,000 per week in credits by doing this. i am trying to determine what the net preset value of this $5,000 per week would be in three years. i hope that helps. i am lost on how to create the spreadsheet.

thanks in advance!