SoftArtisans ExcelWriter Guide

All Functions Math Functions String Functions Logical Functions
Time Functions Statistical Functions Information Functions
Lookup & Reference Functions Database Functions Financial Functions

Financial Functions

The only functions available in
ExcelWriterSE and ExcelWriterLE
are sum and average.

Fv*
Npv*
Pmt*
Pv*

Financial Functions

Fv*

Returns the future value of an investment of fixed regular payments, at a fixed rate of interest. Fv takes five arguments:

Rate	Fixed interest rate per payment period
Nper	Number of payment periods
Pmt	Fixed payment amount per period
[Pv]	Present value of the investment. Optional. Default value = 0.
[Type]	Payment timing. 1 = the beginning of the payment period; 0 = the end of the period. Optional. Default value = 0.

The following will display the future value of an investment at 6% interest compounded monthly (0.5%/month), of eighteen payments of $150, with an initial value of $1000, where payments are made at the beginning of each period.

	ws.cells("B2").formula = "=FV(0.005,18,-150,-1000,1)"

Note: Represent payments as negative numbers, and money received as positive.

Npv*

Returns the net present value of an investment based on a discount rate and a series of future payments (negative values) and income (positive values). Npv takes at least two arguments:

Rate	The rate of discount over the length of one period.
Value1	Value1, value2... up to 29 values. Payments and income, equally spaced in time and occurring at the end of each period. Be sure to enter payment and income values in the correct sequence because the order of the values is used to interpret the order of cash flows.

The following will display the net present value of an investment with an annual discount rate of 10%, in which you pay $10,000 one year from today and receive an annual income of $3,000, $4,200 and $6,800 in the three years that follow.

	ws.cells("B2").formula = "=NPV(0.10, -10000, 3000, 4200, 6800)"

Pmt*

Returns the amount of each loan payment, where both payments and interest rate are constant. Pmt takes five arguments:

Rate	The loan's fixed interest rate
Nper	Number of loan payments
Pv	The present value - or, principal - of the loan
[Fv]	Future value of the loan, that is, the desired remaining balance after the last payment. Optional. Default value = 0.
[Type]	Payment timing. 1 = the beginning of the payment period; 0 = the end of the period. Optional. Default value = 0.

The following will display the monthly payment on a $15000 loan, at 8% interest per year, to be paid off in 24 months, with payments made at the beginning of each period.

	ws.cells("B2").formula = "=PMT(0.08/12,24,15000,0,1)"

Note: Represent payments as negative numbers, and money received as positive.

Pv*

Returns the present value of an investment, where both payments and interest rate are constant. Pv takes five arguments:

Rate	Fixed interest rate per period
Nper	Number of payment periods
[Pmt]	Fixed payment amount per period. Optional. If you omit Pmt, include Fv.
[Fv]	Future value of the investment, that is, the desired balance after the last payment. Optional. Default value = 0. If you omit Fv, include Pmt.
[Type]	Payment timing. 1 = the beginning of the payment period; 0 = the end of the period. Optional. Default value = 0.

The following will display the cost of a 25 year insurance annuity, with payments of $1000 at the beginning of each month, earning 8% interest per year.

	ws.cells("B2").formula = "=PV(0.08/12,12*25,1000,0,1)"

*This function is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree. The only functions available in ExcelWriterSE, ExcelWriterLE, and ExcelWriterFree are sum and average.