1. Use Goal Seek on this worksheet to calculate what annual return should be to achieve a Net Present Value of $100,000 with an initial payment of $500,000 and the same annual return coming at the end of the first, second, third, and fourth years. Use a 10% discount rate. Note that a single cell can be referenced for each year's annual return.
2. Then generate a data table on another worksheet of Net Present Value for Discount Rates of 7%, 8%, 9%, 10%, 11% and 12%, and the Year Return for each of four years following the investment of $150K, $175K, $200K, $225K, $250K and $275K. (K represents a thousand). The initial investment remains $500,000.