Remember that: P = F x (1 + i)^-n is the model, and that:
• i is the interest or discount rate per accounting period.
• n is the number of accounting periods from 0, there may be several n's.
• P is a Present Value at time 0, may be positive or negative.
• F is a Future Value at a time n, may be positive or negative.
• A is a uniform amount per period for n periods, may be positive or negative.

The most common Compound Interest Factors (Functions) are:

• F/P Single payment Future Value, or Capital Accumulating Factor (CAF).
• P/F Single payment Present Value, or Present Value Factor (PVF).
  Note: P/F factor is reciprical of F/P.

• A/P Uniform Series payments (Annuity) equivalent of a Present Value, or Capital Recovery Factor (CRF).
• P/A Present Value of a series of payments factor (SPVF).
  Note: P/A factor is reciprical of A/P.

• A/F Uniform series of Payments to produce a Future Value, or Sinking Fund Factor (SFF).
• F/A Future Value that is equivalent of a uniform series of payments
  Note: A/F is reciprical of F/A

Spred sheets are best suited to two dimensional arrays. The P/F and F/P factors deal with single values. One of the ways P/F could be entered is shown below:

If three cells auch as A1, A2, and A3 are dedicated to values for i, n, and either P or F then A4 can contain the expression for the desired outcome.
E.g. If you want to calculate P given i, n, and F then enter i an A1, n in A2 and F in A3. Then enter =A3*(1+A1)^-A2 in A4. The Present Value P will then appear in A4 for whatever values of i, n, and F are entered.

A similar pattern for F/P would enter a value of P in A3 and to change A4 to =A3*(1+A1)^A2

The traditional proceedure for finding the Present Value of a non uniform series of payments was to find the present value of each of them and then to sum the results. This can be done in EXCEL by using the function NPV (Net Present Value).

A template for computing NPV can be constructed as follows:
Use column A to record the payments for the end of periods 1 to n. Use the row numbers to post the payments in the proper time locations. Use - amounts for disbursements. Post the interest or discount rate i in B1. In B2 enter =NPV(B1,A1:A20) The Net Present Value will appear in B2.
Note: by using A1:A20 we make a template that allows for up to 20 payments. The range should be adjusted to equal or slightly exceed the number of payments and there should be no quantities apart from the payments entered in this range of cells.

Links:

1. Note on Discrete End of Period Compound Interest Model
2. Compound Interest Calculations
3. Time Value and Engineering Economy Topics
4. Index of MYNET files

End to date, ams 980422