Financial Functions

As discussed above, several financial functions use the Data Set values. These functions and all other functions in Napier expect percentages to be entered as percent values, not fractions and will return percentages the same way. That is, for 10%, use the value 10, not .1 (there is no % key).

Graduated Rate Schedules

Many financial operations involve tables of rates and values, e.g. US Federal Income Tax, Social Security Withholding, and business discount and commission schedules. In the Options dialog, there is a button captioned Grad Rates which allowed editing the 8 graduated rate schedules.

This is what the Grad Rate dialog looks like:

Each of the 8 graduated rate schedules can have up to 10 brackets, if you donít need all 10, just leave the ceiling blank. The last bracket also has a blank ceiling which means that its bracket applies to all values from there up. The floor of each bracket is the ceiling of the previous bracket, the floor of the first bracket is a very large negative number, so if you enter a negative value in the example above, you will get back a value of 0 because the first bracketís percentage is 0 and any value below 10000, including all negative values will have a rate of 0 applied. The ceiling of the last bracket can either be explicitly entered, in which case, all values above that are 0%, or left blank, in which case all large values have the last bracketís percentage applied. In the US, Social Security withholding has a maximum bracket ceiling, but Federal Income Tax doesnít.

You can enter negative percentages and ceilings if you can come up with a use for them. For negative amounts entered, the value is based on the sum of the amounts from all the negative brackets up to a value of zero. For positive amounts, the value is from zero up to the amount.

Bracket ceilings must be in ascending order

All the Graduated Rate Functions

GRVal

 

 

GRVal(which, amount)

 

which: index of grad rate def

 

Sums brackets up to amount's

 

and calcs amount in top bracket

 

Uses entries in Grad Rates in Options

GRMax

 

 

GRMax(which, amount)

 

 = value from max bracket

 

which: index of grad rate def

 

Uses max bracket's percent on amount

 

Uses entries in Grad Rates in Options

GRPct

 

 

GRPct(which, amount)

 

 = percent of bracket

 

which: index of grad rate def

 

Amount is bracket, returns it's percent

 

Uses entries in Grad Rates in Options

GRCl

 

 

GRCl(which, amount)

 

 = ceiling of bracket

 

which: index of grad rate def

 

amount: selects bracket

 

Uses entries in Grad Rates in Options

GRFl

 

 

GRFl(which, amount)

 

 = floor of bracket

 

which: index of grad rate def

 

amount: selects bracket

 

Uses entries in Grad Rates in Options

 

These all expect the index (1 ñ 8) of the graduated rate as their first argument.

The graduated rate schedules can be saved and read back separately from the rest of the state, so you can have several sets of 8 schedules which can be read in as needed. This is discussed more below.

Loan Calculations

There are actually two sets of functions that can be used for loan calculation, the more general set will be discussed below. This set is for normal loan calculations where the term is in years, the interest rate is yearly, and the entire amount is to be paid off at the end of the loan period. For these calculations, all values are positive.

These calculate the amount of the loan, the monthly payments, the term in years, or the yearly percentage given the other 3., or the total interest or principle from the start to a given payment. Note that the interest rate calculation is done by trying values until one is found that matches the input values. There can be more than one answer possible. If it canít find an answer in a reasonable number of tries, it will return an error.

LAmt

 

 

LAmt(mpay, yi, yrs) = amt

 

mpay: mo pay, yi: yr rate, yrs: yrs

 

Loan amount for mpay monthly

 

payments for yrs years

 

Amounts are all + for normal loans

 

yi is per year. If 8.5%, use 8.5

LPay

 

 

LPay(amt, yi, yrs) = pay

 

amt: loan, yi: yr rate, yrs: yrs

 

Loan monthly payments for yrs years

 

Amounts are all + for normal loans

 

yi is per year. If 8.5%, use 8.5

LYrs

 

 

LYrs(amt, mpay, yi) = yrs

 

amt: loan, mpay: mo pay,

 

yi: yr rate

 

Years to pay a loan

 

of amt with mpay monthly payments

 

and yearly int rate of yi

 

Amounts are all + for normal loans

 

yi is per year. If 8.5%, use 8.5

LInt

 

 

LInt(amt, mpay, yrs) = yi

 

amt: loan, mpay: mo pay,

 

yrs: years

 

Yearly int rate

 

for loan of amt with mpay monthly

 

payments for yrs years

 

Amounts are all + for normal loans

 

This is yearly %, 8.5 = 8.5%

LTotI

 

 

LTotI(amt, yi, yrs, yr)

 

 = total int paid from start to yr

 

amt: loan, yi: yr rate, yrs: years,

 

yr: year

 

Int paid to end of year yr

 

for loan of amt with yi yearly

 

interest for yrs years. Accurate to

 

1 month (yr can be fractional)

 

Amounts are all + for normal loans

LTotP

 

 

LTotP(amt, yi, yrs, yr)

 

 = Total principle paid to yr

 

amt: loan, yi: yr rate, yrs: years,

 

yr: year

 

Princ paid to end of year yr

 

for loan of amt with yi yearly

 

interest for yrs years. Accurate to

 

1 month (yr can be fractional)

 

Amounts are all + for normal loans

 

General Loan / Investment Functions

These functions are more complex to use than the above Loan functions, but are much more general. For all of these functions, all amounts that you pay are negative, all amounts you receive are positive. It is critical that you follow this standard, or the results will be very different from what you want.

The payments and the interest rate must be for the same period. If you enter the interest rate as a yearly value, the payments must also be yearly and for a loan are negative because you pay them.

PV or Present Value is the amount you start with, FV or Future Value is what is left at the end. For a normal loan, the PV is the amount of the loan you receive and is positive because you get it. The FV is zero because it will be completely paid off at the end

There are a lot of sources of further discussion of the use of these formulas in books on money management, and on the net. Here is what Napier has for these functions:

FV

 

 

FV(pv, pmt, i, n)

 

pv: start, pmt: payments, i: rate,

 

n: periods

 

Future balance of

 

an account starting with pv

 

balance, making pmt payments

 

Values are - if you pay, + if

 

you receive, for all entries

 

i is per period. If 8.5%, use 8.5

PV

 

 

PV(fv, pmt, i, n)

 

fv: end, pmt: payments, i: rate,

 

n: periods

 

Present balance of

 

an account needed to get to a

 

fv balance, in n periods,

 

making periodic pmt payments

 

Values are - if you pay, + if

 

you receive, for all entries

 

i is per period. If 8.5%, use 8.5

NPER

 

 

NPER(pv, fv, pmt, i)

 

pv: start $, fv: end $, pmt: payment

 

i: rate

 

Number of periods to get to

 

a fv balance with a pv starting

 

balance making pmt payments

 

Values are - if you pay, + if

 

you receive, for all entries

 

i is per period. If 8.5%, use 8.5

PMT

 

 

PMT(pv, fv, i, n)

 

pv: start, fv: end i: rate,

 

n: periods

 

Payments to an account

 

starting with pv balance to get

 

to fv  balance making pmt payments

 

Values are - if you pay, + if

 

you receive, for all entries

 

i is per period. If 8.5%, use 8.5

RATE

 

 

RATE(pv, fv, pmt, n)

 

pv: start, fv: end, pmt: payment,

 

n: periods

 

Interest rate for an

 

account starting with pv balance

 

to get to fv balance, making pmt

 

payments for n periods.

 

Values are - if you pay, + if

 

you receive, for all entries

 

Rate is per period. If 8.5%, use 8.5

BAL

 

 

BAL(pv, i, n, p)

 

pv: loan amount, i: rate, n: periods

 

p: periods paid.

 

= Balance at period p

 

Values are - if you pay, + if

 

you receive, for all entries

 

Balance remaining after p periods

 

i is per period. If 8.5%, use 8.5

TOTI

 

 

TOTI(pv, i, n, s, e)

 

pv: loan amount, i: rate, n: periods

 

s: start, e: end periods to use

 

Interest paid from periods s to e

 

Values are - if you pay, + if

 

you receive, for all entries

 

i is per period. If 8.5%, use 8.5

TOTP

 

 

TOTP(pv, i, n, s, e)

 

pv: loan amount, i: rate, n: periods

 

s: start, e: end periods to use

 

Principle paid from periods s to e

 

Values are - if you pay, + if

 

you receive, for all entries

 

i is per period. If 8.5%, use 8.5

 

Bond Functions

These fall into 4 categories, instruments which use day counts based on the 30/360 basis, and those that use actual day counts, coupon paying and zero coupons. The Napier bond functions all have B as their first character for coupon bonds, Z for zeros. They have 3 as their last character for 30/360 basis, and A for Actual/Actual basis. For example, BYld3 calculates the yield for coupon bearing bonds using the 30/360 basis, while ZYldA does yields for zeros using Act/Act basis. There is a 30/360 day count function in the time function list.

BCpnN

 

 

BCpnN(sd,md,frq)

 

= coupon count

 

sd: settle date, md: mature date

 

frq: coupon frequency

BPrcC3

 

 

BPrcC3(sd,md,mv,cr,frq,yld)

 

= clean bond price 30/360

 

sd: settle date, md: mature date

 

mv: maturity value

 

cr: coupon rate % 8.5 not .085

 

frq: coupon frequency

 

yld: yield % 8.5 not .085

BPrcF3

 

 

BPrcF3(sd,md,mv,cr,frq,yld)

 

= full bond price 30/360

 

sd: settle date, md: mature date

 

mv: maturity value

 

cr: coupon rate % 8.5 not .085

 

frq: coupon frequency

 

yld: yield % 8.5 not .085

BPrcCA

 

 

BPrcCA(sd,md,mv,cr,frq,yld)

 

= clean bond price ACT/ACT

 

sd: settle date, md: mature date

 

mv: maturity value

 

cr: coupon rate % 8.5 not .085

 

frq: coupon frequency

 

yld: yield % 8.5 not .085

BPrcFA

 

 

BPrcFA(sd,md,mv,cr,frq,yld)

 

= full bond price ACT/ACT

 

sd: settle date, md: mature date

 

mv: maturity value

 

cr: coupon rate % 8.5 not .085

 

frq: coupon frequency

 

yld: yield % 8.5 not .085

BYld3

 

 

BYld3(sd,md,mv,cr,frq,prc)

 

= yield 30/360

 

sd: settle date, md: mature date

 

mv: maturity value

 

cr: coupon rate % 8.5 not .085

 

frq: coupon frequency

 

prc: purchase price

BYldA

 

 

BYldA(sd,md,mv,cr,frq,prc)

 

= bond yield ACT/ACT

 

sd: settle date, md: mature date

 

mv: maturity value

 

cr: coupon rate % 8.5 not .085

 

frq: coupon frequency

 

prc: purchase price

ZPrc3

 

 

ZPrc3(sd,md,mv,frq,yld)

 

= zero price 30/360

 

sd: settle date, md: mature date

 

mv: maturity value

 

frq: coupon frequency

 

yld: yield % 8.5 not .085

ZPrcA

 

 

ZPrcA(sd,md,mv,frq,yld)

 

= zero price ACT/ACT

 

sd: settle date, md: mature date

 

mv: maturity value

 

frq: coupon frequency

 

yld: yield % 8.5 not .085

ZYld3

 

 

ZYld3(sd,md,mv,frq,prc)

 

= zero yield 30/360

 

sd: settle date, md: mature date

 

mv: maturity value

 

frq: coupon frequency

 

prc: purchase price

ZYldA

 

 

ZYldA(sd,md,mv,frq,prc)

 

= zero yield ACT/ACT

 

sd: settle date, md: mature date

 

mv: maturity value

 

frq: coupon frequency

 

prc: purchase price

BDur3

 

 

BDur3(sd,md,mv,cr,frq,yld)

 

= Macaulay Duration 30/360

 

sd: settle date, md: mature date

 

mv: maturity value

 

cr: coupon rate % 8.5 not .085

 

frq: coupon frequency

 

yld: yield % 8.5 not .085

BDurA

 

 

BDurA(sd,md,mv,cr,frq,yld)

 

= Macaulay Duration ACT/ACT

 

sd: settle date, md: mature date

 

mv: maturity value

 

cr: coupon rate % 8.5 not .085

 

frq: coupon frequency

 

yld: yield % 8.5 not .085

ZDur3

 

 

ZDur3(sd,md,mv,frq,yld)

 

Zero Macaulay Duration 30/360

 

sd: settle date, md: mature date

 

mv: maturity value

 

cr: coupon rate % 8.5 not .085