First Built-in Functions in 1979 VisiCalc: https://www.classic-computers.org.

nz/system-80/software-manuals/m
SUM @SUM
NA @NA
ERROR @ERROR
MAX @MAX
MIN @MIN
AVERAGE @AVERAGE
COUNT @COUNT
NPV @NPV Page 81 In the original VisiCalc manual
LOOKUP @LOOKUP
ABS @ABS
INT @INT
EXP @EXP
LOG10 @LOG10
LN @LN
PI @PI
SIN @SIN @SUM, @NA, @ERROR, @MAX, @MIN, @AVERAGE, @CO
COS @COS
TAN @TAN
ASIN @ASIN
ACOS @ACOS
ATAN @ATAN
em-80/software-manuals/manuals-visicalc.pdf

Numbers
3
2
4
2
4
15

@MIN, @AVERAGE, @COUNT, @NPV, @LOOKUP, @ABS, @INT, @EXP, @LOG10, @LN, @PI, @SIN, @COS, @TAN, @ASIN, @ACOS, @AT
TAN, @ASIN, @ACOS, @ATAN
Function
ACCRINT function
ACCRINTM function
AMORDEGRC function
AMORLINC function
COUPDAYBS function
COUPDAYS function
COUPDAYSNC function
COUPNCD function
COUPNUM function
COUPPCD function
CUMIPMT function
CUMPRINC function

DB function

DDB function
DISC function
DOLLARDE function
DOLLARFR function
DURATION function
EFFECT function
FV function
FVSCHEDULE function
INTRATE function
IPMT function
IRR function
ISPMT function
MDURATION function

MIRR function
NOMINAL function
NPER function

NPV function
ODDFPRICE function
ODDFYIELD function
ODDLPRICE function
ODDLYIELD function
PDURATION function
PMT function
PPMT function
PRICE function
PRICEDISC function
PRICEMAT function
PV function
RATE function
RECEIVED function
RRI function
SLN function
SYD function
TBILLEQ function
TBILLPRICE function
TBILLYIELD function

VDB function
XIRR function
XNPV function
YIELD function
YIELDDISC function
YIELDMAT function
Description
Returns the accrued interest for a security that pays periodic interest
Returns the accrued interest for a security that pays interest at maturity
Returns the depreciation for each accounting period by using a depreciation coefficient
Returns the depreciation for each accounting period
Returns the number of days from the beginning of the coupon period to the settlement date
Returns the number of days in the coupon period that contains the settlement date
Returns the number of days from the settlement date to the next coupon date
Returns the next coupon date after the settlement date
Returns the number of coupons payable between the settlement date and maturity date
Returns the previous coupon date before the settlement date
Returns the cumulative interest paid between two periods
Returns the cumulative principal paid on a loan between two periods

Returns the depreciation of an asset for a specified period by using the fixed-declining balance method
Returns the depreciation of an asset for a specified period by using the double-declining balance method or some
other method that you specify
Returns the discount rate for a security
Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
Returns the annual duration of a security with periodic interest payments
Returns the effective annual interest rate
Returns the future value of an investment
Returns the future value of an initial principal after applying a series of compound interest rates
Returns the interest rate for a fully invested security
Returns the interest payment for an investment for a given period
Returns the internal rate of return for a series of cash flows
Calculates the interest paid during a specific period of an investment
Returns the Macauley modified duration for a security with an assumed par value of $100

Returns the internal rate of return where positive and negative cash flows are financed at different rates
Returns the annual nominal interest rate
Returns the number of periods for an investment

Returns the net present value of an investment based on a series of periodic cash flows and a discount rate
Returns the price per $100 face value of a security with an odd first period
Returns the yield of a security with an odd first period
Returns the price per $100 face value of a security with an odd last period
Returns the yield of a security with an odd last period
Returns the number of periods required by an investment to reach a specified value
Returns the periodic payment for an annuity
Returns the payment on the principal for an investment for a given period
Returns the price per $100 face value of a security that pays periodic interest
Returns the price per $100 face value of a discounted security
Returns the price per $100 face value of a security that pays interest at maturity
Returns the present value of an investment
Returns the interest rate per period of an annuity
Returns the amount received at maturity for a fully invested security
Returns an equivalent interest rate for the growth of an investment
Returns the straight-line depreciation of an asset for one period
Returns the sum-of-years' digits depreciation of an asset for a specified period
Returns the bond-equivalent yield for a Treasury bill
Returns the price per $100 face value for a Treasury bill
Returns the yield for a Treasury bill

Returns the depreciation of an asset for a specified or partial period by using a declining balance method
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic
Returns the net present value for a schedule of cash flows that is not necessarily periodic
Returns the yield on a security that pays periodic interest
Returns the annual yield for a discounted security; for example, a Treasury bill
Returns the annual yield of a security that pays interest at maturity
Total 55
FUNCTION
ACCRINT
ACCRINTM

AMORDEGRC

AMORLINC
COUPDAYBS
COUPDAYS
COUPDAYSNC
COUPNCD

COUPNUM
COUPPCD
CUMIPMT
CUMPRINC
DB

DDB
DISC

DOLLARDE

DOLLARFR

DURATION

EFFECT

FV
INTRATE

IPMT

ISPMT
MDURATION

NOMINAL

NPER
ODDFPRICE
ODDFYIELD
ODDLPRICE
ODDLYIELD
PDURATION
PMT

PPMT
PRICE
PRICEDISC
PRICEMAT

PV

RATE
RECEIVED
RRI
SLN
SYD
TBILLEQ
TBILLPRICE
TBILLYIELD

VDB
XIRR
XNPV
YIELD
YIELDDISC
YIELDMAT
DESCRIPTION
Returns the accrued interest for a security that pays periodic interest.
Returns the accrued interest for a security that pays interest at maturity.

Returns the depreciation for each accounting period. This function is provided for the French accounting system. If an asset is
purchased in the middle of the accounting period, the prorated depreciation is taken into account. The function is similar to
AMORLINC, except that a depreciation coefficient is applied in the calculation depending on the life of the assets.

Returns the depreciation for each accounting period. This function is provided for the French accounting system. If an asset is
purchased in the middle of the accounting period, the prorated depreciation is taken into account.
Returns the number of days from the beginning of a coupon period until its settlement date.
Returns the number of days in the coupon period that contains the settlement date.
Returns the number of days from the settlement date to the next coupon date.
Returns the next coupon date after the settlement date.
Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole
coupon.
Returns the previous coupon date before the settlement date.
Returns the cumulative interest paid on a loan between start_period and end_period.
Returns the cumulative principal paid on a loan between start_period and end_period.
Returns the depreciation of an asset for a specified period using the fixed-declining balance method.
Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method
you specify.
Returns the discount rate for a security.
Converts a dollar price expressed as an integer part and a fraction part, such as 1.02, into a dollar price expressed as a
decimal number. Fractional dollar numbers are sometimes used for securities prices.
Converts a dollar price expressed as a decimal number into a dollar price expressed as an integer part and a fraction part,
such as 1.02. Fractional dollar numbers are sometimes used for securities prices.
Returns the Macauley duration for an assumed par value of $100. Duration is defined as the weighted average of the present
value of cash flows, and is used as a measure of a bond price's response to changes in yield.
Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods
per year.
Calculates the future value of an investment based on a constant interest rate. You can use FV with either periodic, constant
payments, or a single lump sum payment.
Returns the interest rate for a fully invested security.
Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant
interest rate.

Calculates the interest paid (or received) for the specified period of a loan (or investment) with even principal payments.
Returns the modified Macauley duration for a security with an assumed par value of $100.

Returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year.

Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
Returns the price per $100 face value of a security having an odd (short or long) first period.
Returns the yield of a security that has an odd (short or long) first period.
Returns the price per $100 face value of a security having an odd (short or long) last period.
Returns the yield of a security that has an odd (short or long) last period.
Returns the number of periods required by an investment to reach a specified value.
Calculates the payment for a loan based on constant payments and a constant interest rate.
Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a
constant interest rate.
Returns the price per $100 face value of a security that pays periodic interest.
Returns the price per $100 face value of a discounted security.
Returns the price per $100 face value of a security that pays interest at maturity.

calculates the present value of a loan or an investment, based on a constant interest rate. You can use PV with either
periodic, constant payments (such as a mortgage or other loan), or a future value that's your investment goal.

Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the
successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns an error.
Returns the amount received at maturity for a fully invested security.
Returns an equivalent interest rate for the growth of an investment.
Returns the straight-line depreciation of an asset for one period.
Returns the sum-of-years' digits depreciation of an asset for a specified period.
Returns the bond-equivalent yield for a Treasury bill.
Returns the price per $100 face value for a Treasury bill.
Returns the yield for a Treasury bill.
Returns the depreciation of an asset for any period you specify, including partial periods, using the double-declining balance
method or some other method you specify. VDB stands for variable declining balance.
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
Returns the net present value for a schedule of cash flows.
Returns the yield on a security that pays periodic interest. Use YIELD to calculate bond yield.
Returns the annual yield for a discounted security.
Returns the annual yield of a security that pays interest at maturity.
Count 51
PMT function from borrower point of view
Price of car 34,799.00
Down payment 10,000.00
Annual interest rate 6.50%
Years for loan 5
Compounding periods per year 12
Period rate = monthly interest rate = rate 0.54% =B4/B6
Total number of periods = total months = nper 60 =B5*B6
Present value = loan amount = pv 24,799.00 =B2-B3
Periodic PMT = Monthly Payment = pmt -485.22 =PMT(B7,B8,B9)
Periodic PMT = Monthly Payment = pmt -485.22 =-B9/((1-(1+B4/B6)^(-B5*B6))/(B4/B6))
Q #1 How much will $10,000 invested today be worth in 10 years at an APR of 5.00%, compounded monthly?
Variables and function arguments: Inputs & formulas:
Amount to invest today = pv $10,000.00
Annual interest rate = APR 0.05
Years 10
Compounding periods per year 12
Period rate = rate
Total number of periods = nper
Future value = fv

FV( rate , nper , pmt , [pv] , [type] )

Future value is positive Period rate
because money is
coming into wallet / Present value must be negative because
purse. This is a Total number of periods $ coming out of wallet / purse
withdrawal.
No pmt, so argument skipped
n 10 years at an APR of 5.00%, compounded monthly?
Formulas:

, pmt , [pv] , [type] )

No annuity so type is omitted
Present value must be negative because
$ coming out of wallet / purse
t, so argument skipped
daily 365
monthly 12
quarterly 4
yearly 1
Q #1 How much will $10,000 invested today be worth in 10 years at an APR of 5.00%, compounded monthly?
Variables and function arguments: Inputs & formulas:
Amount to invest today = pv $10,000.00
Annual interest rate = APR 0.05
Years 10
Compounding periods per year 12
Period rate = rate 0.00416666666666667
Total number of periods = nper 120
Future value = fv $16,470.09

FV( rate , nper , pmt , [pv] , [type] )

Future value is positive Period rate
because money is
coming into wallet / Present value must be negative because
purse. This is a Total number of periods $ coming out of wallet / purse
withdrawal.
No pmt, so argument skipped

Math formula fv: $16,470.09

n 10 years at an APR of 5.00%, compounded monthly?
Formulas:

=C6/C8
=C7*C8
=FV(C9,C10,,-C5)

, pmt , [pv] , [type] )

No annuity so type is omitted
Present value must be negative because
$ coming out of wallet / purse
t, so argument skipped

=C5*(1+C9)^C10
daily 365
monthly 12
quarterly 4
yearly 1
Q #2 If you invest $50 at the end of each month with an estimated APR of 13.00%, how much will you have in 40 years?
Variables and function arguments: Inputs & formulas:
Amount in stock account at time 0 = pv $250.00
Amount to invest each month $50.00
Annual interest rate = APR 0.13
Years 40
Compounding periods per year 12
Type of annuity: End = 0 or omit, Begin = 1
Period rate = rate
Total number of periods = nper
Future value = fv
Total deposited
Total return gained

FV( rate , nper , pmt , [pv] , [type] )

Future value is positive Period rate
because money is
coming into wallet /
purse. This is a Total number of periods
withdrawal.
Payment is negative because money
comes out of wallet / purse
ed APR of 13.00%, how much will you have in 40 years?
Formulas:
Starting account balance at time zero

pmt , [pv] , [type] )

Annuity is End, so type is omitted
Present value is negative because
money comes out of wallet / purse
gative because money
wallet / purse
daily 365

monthly 12
quarterly 4
yearly 1
Q #2 If you invest $50 at the end of each month with an estimated APR of 13.00%, how much will you have in 40 years?
Variables and function arguments: Inputs & formulas:
Amount in stock account at time 0 = pv $250.00
Amount to invest each month $50.00
Annual interest rate = APR 0.13
Years 40
Compounding periods per year 12
Type of annuity: End = 0 or omit, Begin = 1
Period rate = rate 0.0108333333333333
Total number of periods = nper 480
Future value = fv $853,021.58
Total invested $24,250.00
Total return gained $828,771.58

FV( rate , nper , pmt , [pv] , [type] )

Future value is positive Period rate
because money is
coming into wallet /
purse. This is a Total number of periods
withdrawal.
Payment is negative because money
comes out of wallet / purse

Math formula fv: $853,021.58

ed APR of 13.00%, how much will you have in 40 years?
Formulas:
Starting account balance at time zero

=C7/C9
=C8*C9
=FV(C11,C12,-C6,-C5)
=C6*C12+C5
=C13-C14

pmt , [pv] , [type] )

Annuity is End, so type is omitted
Present value is negative because
money comes out of wallet / purse
gative because money
wallet / purse

=C6*(((1+C11)^C12-1)/C11)+C5*(1+C11)^C12
daily 365

monthly 12
quarterly 4
yearly 1
Q #3 How much should be deposited today, to have $50,000 in 18 years at an APR of 6.75%, compounded daily?
Variables and function arguments: Inputs & formulas:
Amount needed in 18 years = fv $50,000.00
Annual interest rate = APR 0.0675
Years 18
Compounding periods per year 365
Period rate = rate
Total number of periods = nper
Present value = pv

PV( rate , nper , pmt , [fv] , [type] )

Present value is Period rate
negative because
money is coming Future value must be positive because
out of wallet / Total number of periods money is coming into wallet / purse
purse. This is a
deposit. No pmt, so argument skipped
0 in 18 years at an APR of 6.75%, compounded daily?
Formulas:
fv

pmt , [fv] , [type] )

No annuity so type is omitted
Future value must be positive because
money is coming into wallet / purse
so argument skipped
daily 365
monthly 12
quarterly 4
yearly 1
Q #3 How much should be deposited today, to have $50,000 in 18 years at an APR of 6.75%, compounded daily?
Variables and function arguments: Inputs & formulas:
Amount needed in 18 years =fvfv $50,000.00
Annual interest rate = APR 0.0675
Years 18
Compounding periods per year 365
Period rate = rate 0.000184931506849315
Total number of periods = nper 6570
Present value = pv -$14,837.17

PV( rate , nper , pmt , [fv] , [type] )

Present value is Period rate
negative because
money is coming out Future value must be positive because
of wallet / purse. Total number of periods money is coming into wallet / purse
This is a deposit.
No pmt, so argument skipped

Math formula pv: -$14,837.17

0 in 18 years at an APR of 6.75%, compounded daily?
Formulas:

=C6/C8
=C7*C8
=PV(C9,C10,,C5)

pmt , [fv] , [type] )

No annuity so type is omitted
Future value must be positive because
money is coming into wallet / purse
so argument skipped

=-C5/(1+C9)^C10
daily 365
monthly 12
quarterly 4
yearly 1
Q #4 How much to deposited today, to withdraw $3,500 at the beginning of each month for the next 25 years and has an APR
Variables and function arguments: Inputs & formulas:
Withdrawal amount at the beginning of each month = pmt $3,500.00
Annual interest rate = APR 0.025
Years 25
Compounding periods per year 12
Type of annuity: End = 0 or omit, Begin = 1 1
Period rate = rate
Total number of periods = nper
Present value = pv
Total withdrawn over 25 years
Total return earned
Present value is
negative because PV( rate , nper , pmt , [fv] , [type] )
money is coming
out of wallet / Period rate Annuity is Begin so put 1 into type
purse. This is a
deposit. Total number of periods No fv, so argument skipped

Payment is positive because money

goes into wallet / purse
onth for the next 25 years and has an APR of 2.50%?
Formulas:

, [type] )
Annuity is Begin so put 1 into type
No fv, so argument skipped

cause money
e
daily 365
monthly 12
quarterly 4
yearly 1
Q #4 How much to deposited today, to withdraw $3,500 at the beginning of each month for the next 25 years and has an APR
Variables and function arguments: Inputs & formulas:
Withdrawal amount at the beginning of each month = pmt $3,500.00
Annual interest rate = APR 0.025
Years 25
Compounding periods per year 12
Type of annuity: End = 0 or omit, Begin = 1 1
Period rate = rate 0.00208333333333333
Total number of periods = nper 300
Present value = pv -$781,801.35
Total withdrawn over 25 years $1,050,000.00
Total return earned $268,198.65

Present value is
negative because PV( rate , nper , pmt , [fv] , [type] )
money is coming
out of wallet / Period rate Annuity is Begin so put 1 into type
purse. This is a
deposit. Total number of periods No fv, so argument skipped

Payment is positive because money

goes into wallet / purse

Math formula for pv: -$781,801.35

onth for the next 25 years and has an APR of 2.50%?
Formulas:

=C6/C8
=C7*C8
=PV(C10,C11,C5,,C9)
=C11*C5
=C13+C12

, [type] )
Annuity is Begin so put 1 into type
No fv, so argument skipped

cause money
e

=-C5*((1-(1+C10)^-(C11-1))/C10)-C5
daily 365
monthly 12
quarterly 4
yearly 1
Q #5 How much do you need to invest at the end of each month if you want $781,801.35 on the day you retire and assume a
Variables and function arguments: Inputs & formulas:
Amount to have on day of retirement = fv $781,801.35
Annual interest rate = APR 0.1
Years 35
Compounding periods per year 12
Type of annuity: End = 0 or omit, Begin = 1
Period rate = rate
Total number of periods = nper
Monthly investment amount = pmt
Total deposited over 35 years
Total return earned
Payment is negative
because money is PMT( rate , nper , pv , [fv] , [type] )
coming out of wallet /
purse. This is a monthly Period rate Annuity is End, so type is omitted
payment.
Total number of periods Future value is positive because money is com
No pv, so argument skipped
1.35 on the day you retire and assume a 10.00% APR?
Formulas:

, [type] )
Annuity is End, so type is omitted
ure value is positive because money is coming into wallet / purse
ument skipped
daily 365
monthly 12
quarterly 4
yearly 1
Q #5 How much do you need to invest at the end of each month if you want $781,801.35 on the day you retire and assume a
Variables and function arguments: Inputs & formulas:
Amount to have on day of retirement = fv $781,801.35
Annual interest rate = APR 0.1
Years 35
Compounding periods per year 12
Type of annuity: End = 0 or omit, Begin = 1
Period rate = rate 0.00833333333333333
Total number of periods = nper 420
Monthly investment amount = pmt -$205.92
Total invested over 35 years $86,486.14
Total return earned $695,315.21

Payment is negative
because money is PMT( rate , nper , pv , [fv] , [type] )
coming out of wallet /
purse. This is a monthly Period rate Annuity is End, so type is omitted
payment.
Total number of Future value is positive because money is co
periods wallet / purse
No pv, so argument
skipped

Math formula for pmt: -$205.92

1.35 on the day you retire and assume a 10.00% APR?
Formulas:

=C6/C8
=C7*C8
=PMT(C10,C11,,C5)
=C11*-C12
=C5-C13

, [type] )
Annuity is End, so type is omitted
ure value is positive because money is coming into
llet / purse

=-C5/(((1+C10)^C11-1)/C10)
daily 365
monthly 12
quarterly 4
yearly 1
Q #6 What is the monthly payment for a 20-year $206,910.92 home mortgage loan at 3.50% APR?
Q #7 How much does the bank take as interest for each mortgage payment?
Variables and function arguments: Inputs & formulas: Formulas:
Amount of home mortgage loan = pv $206,910.92
Annual interest rate = APR 0.035
Years 20
Compounding periods per year 12
Type of annuity: End = omit
Period rate = rate
Total number of periods = nper
Monthly investment amount = pmt

Amortization Table
Month PMT Interest to bank Reduction on loan
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
 71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
 206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
Totals
Balance Interest for month 1 is the balance from previous m
Interest for month 240 is the balance from previou
 daily 365
monthly 12
quarterly 4
yearly 1

s the balance from previous month*period rate = $0.00*0.0000% = $0.00

40 is the balance from previous month*period rate = $0.00*0.0000% = $0.00
Q #6 What is the monthly payment for a 20-year $206,910.92 home mortgage loan at 3.50% APR?
Q #7 How much does the bank take as interest for each mortgage payment?

Inputs &
Variables and function arguments: formulas: Formulas: Present value is positive because mon
Amount of home mortgage loan = pv is coming into wallet / purse. The bank
$206,910.92
gives the lender the money.
Annual interest rate = APR 0.035
Years 20
Compounding periods per year 12
Interest for month 1 is
Type of annuity: End = omit the balance from
Period rate = rate 0.00291666667 =C7/C9 previous month*period
Total number of periods = nper 240 =C8*C9 rate =
Monthly mortgage payment = pmt -$1,200.00 =PMT(C11,C12,C6)
$206,910.92*0.2917%
= $603.49
Formulas manually copied down:
Formula in cell C24: =-$C$13 rate nper pv
Formula in cell D24: =F23*C$11
Formula in cell E24: =C24-D24 Formula in just 1 cell:
Formula in cell F24: =F23-E24 Formula in cell F23: =C6
Amortization Table
Month PMT Interest to bank Reduction on loan
0
1 $1,200.00 $603.49 $596.51
2 $1,200.00 $601.75 $598.25
3 $1,200.00 $600.01 $599.99
4 $1,200.00 $598.26 $601.74
5 $1,200.00 $596.50 $603.50
6 $1,200.00 $594.74 $605.26
7 $1,200.00 $592.97 $607.03
8 $1,200.00 $591.20 $608.80
9 $1,200.00 $589.43 $610.57
10 $1,200.00 $587.65 $612.35
230 $1,200.00 $37.83 $1,162.17
231 $1,200.00 $34.45 $1,165.55
232 $1,200.00 $31.05 $1,168.95
233 $1,200.00 $27.64 $1,172.36
234 $1,200.00 $24.22 $1,175.78
235 $1,200.00 $20.79 $1,179.21
236 $1,200.00 $17.35 $1,182.65
237 $1,200.00 $13.90 $1,186.10
238 $1,200.00 $10.44 $1,189.56
239 $1,200.00 $6.97 $1,193.03
240 $1,200.00 $3.49 $1,196.51
Totals $288,000.00 $81,089.08 $206,910.92

Interest for month 240 is the balance from previous month*period rate =
$1,196.51*0.2917% = $3.49
Interest for month 240 is the balance from previous month*period rate =
$1,196.51*0.2917% = $3.49
nt value is positive because money
ing into wallet / purse. The bank
he lender the money.

erest for month 1 is

he balance from
vious month*period
rate =
06,910.92*0.2917%
= $603.49

Formula in just 1 cell:

Formula in cell F23: =C6

Balance Interest for month 1 is the balance from previous m

$206,910.92 Interest for month 240 is the balance from previou
$206,314.41
$205,716.16
$205,116.17
$204,514.42
$203,910.92
$203,305.66
$202,698.64
$202,089.84
$201,479.27
$200,866.92
$11,809.72
$10,644.17
$9,475.22
$8,302.85
$7,127.07
$5,947.86
$4,765.20
$3,579.10
$2,389.54
$1,196.51
$0.00

=
=
 daily 365
monthly 12
quarterly 4
yearly 1

s the balance from previous month*period rate = $206,910.92*0.2917% = $603.49

40 is the balance from previous month*period rate = $1,196.51*0.2917% = $3.49
Q #8 What is the monthly payment for a 3-year, $0 car loan with a $5,000 balloon payment and a 5.25% APR?
Variables and function arguments: Inputs & formulas:
Price of car $50,000.00
Down payment on car $5,000.00
Balloon payment at end = fv $5,000.00
Annual interest rate = APR 0.0525
Years 3
Compounding periods per year 12
Type of annuity: End = omit
Loan amount = price - down payment = pv
Period rate = rate
Total number of periods = nper
Monthly car payment = pmt

Payment is negative
because money is PMT( rate , nper , pv , [fv] , [type] )
coming out of wallet / Period rate
purse. This is a monthly
payment. Present value is positive
Total number because money is coming
of periods into wallet / purse. The
bank gives the lender the
money.
th a $5,000 balloon payment and a 5.25% APR?
Formulas:

nper , pv , [fv] , [type] )

Annuity is End, so type is omitted
alue is positive Future value is negative because money is coming
money is coming out of wallet / purse. This is a balloon payment at
et / purse. The the end.
es the lender the
daily 365
monthly 12

quarterly 4
yearly 1
Q #8 What is the monthly payment for a 3-year, $45,000 car loan with a $5,000 balloon payment and a 5.25% APR?
Variables and function arguments: Inputs & formulas:
Price of car $50,000.00
Down payment on car $5,000.00
Balloon payment at end = fv $5,000.00
Annual interest rate = APR 0.0525
Years 3
Compounding periods per year 12
Type of annuity: End = omit
Loan amount = price - down payment = pv $45,000.00
Period rate = rate 0.004375
Total number of periods = nper 36
Monthly car payment = pmt -$1,225.21

Payment is negative
because money is PMT( rate , nper , pv , [fv] , [type] )
coming out of wallet / Period rate
purse. This is a
monthly payment. Total number ofPresent value is positive because
periods money is coming into wallet /
purse. The bank gives the lender
the money.

Math formula for pmt: $1,225.21

oan with a $5,000 balloon payment and a 5.25% APR?
Formulas:

=C5-C6
=C8/C10
=C10*C9
=PMT(C13,C14,C12,-C7)

nper , pv , [fv] , [type] )

Annuity is End, so type is omitted
alue is positive because Future value is negative because
coming into wallet / money is coming out of wallet / purse.
e bank gives the lender This is a balloon payment at the end.
y.

=C12/((1-(1+C13)^-C14)/C13)-C7/(((1+C13)^C14-1)/C13)
daily 365
monthly 12

quarterly 4
yearly 1
Q #9 How long to pay off a credit card bill making only the minimum payment?
Variables and function arguments: Inputs & formulas:
Balance on credit card = pv $7,500.00
Annual interest rate = APR 0.18
Compounding periods per year 12
Minimum monthly payment = pmt 125
Type of annuity: End = omit
Period rate = rate
Total number of periods = nper
Years
Total number of
periods uses the unit NPER( rate , pmt , pv , [fv] , [type] )
of time "months". So
this is: total months Period rate
to pay off loan.
No fv, so argument omitted
Payment is negative
because money is Present value is positive because
coming out of wallet / money is coming into wallet /
purse. This is the purse. The bank gives the lender
monthly payment. the money.
 imum payment?
Formulas:

pv , [fv] , [type] )
Annuity is End, so type is omitted
No fv, so argument omitted
ent value is positive because
ney is coming into wallet /
e. The bank gives the lender
money.
daily 365
monthly 12
quarterly 4
Q #9 How long to pay off a credit card bill making only the minimum payment?
Variables and function arguments: Inputs & formulas:
Balance on credit card = pv $7,500.00
Annual interest rate = APR 0.18
Compounding periods per year 12
Minimum monthly payment = pmt 125
Type of annuity: End = omit
Period rate = rate 0.015
Total number of periods = nper 154.654108565224
Years 12.8878423804353

Total number of
periods uses the unit NPER( rate , pmt , pv , [fv] , [type] )
of time "months". So
this is: total months Period rate
to pay off loan.
No fv, so argument omitted
Payment is negative
because money is Present value is positive because
coming out of wallet / money is coming into wallet /
purse. This is the purse. The bank gives the lender
monthly payment. the money.

Math formula for nper: 154.654108565224

Or:

154.654108565224
154.654108565224

Period Rate 0.1

1 125
2 125
3 125
FV of End Annuity $413.75
NPER 3
 imum payment?
Formulas:

=C6/C7
=NPER(C10,-C8,C5)
=C11/C7

pv , [fv] , [type] )
Annuity is End, so type is omitted
No fv, so argument omitted
ent value is positive because
ney is coming into wallet /
e. The bank gives the lender
money.

=LOG(-(1/(C5/C8*C10-1)),1+C10)

(1+i/n)^n*x = -(1/(PV/PMT*i/n-1)) , then solve for exponent.

=LN(-(1/(C5/C8*C10-1)))/LN(1+C10)
=LN((1-C5*C10/C8)^-1)/LN(1+C10)
daily 365
monthly 12
quarterly 4
Q #10 What is the adjusted APR when there are points and fees for a loan?
Variables and function arguments: Inputs & formulas:
Loan Amount $200,000.00
Annual interest rate = APR 0.05
Years for Loan 30
Periods per Year 12

Points = % of loan amount take as a fee by bank 0.01

Fees = additional fee taken by bank $750.00
Total Fees taken by bank
Amount borrower actually receives
Period rate = rate
Total number of periods = nper

Monthly loan payment based on original amount

(because that is how much is owed) = pmt
Adjusted Period Rate
Adjusted APR

Adjusted period
rate and APR RATE( nper , pmt , pv , [fv] , [type] , [guess] )
reflects the true Period rate
cash flow and
therefore are Payment is negative Annuity is End, so type is omitted
higher than the because money is coming
quoted rates out of wallet / purse. This is No fv, so argument omitted
the monthly payment.
Present value is positive because money is coming into wallet /
purse. The bank gives the lender the money. Borrower must pay
back full 200,000, but only receives $197,250, so for the adjusted
rate you must use the actual cash that was received: $197,250.
 Formulas:

, [type] , [guess] )

No guess, so argument omitted

Annuity is End, so type is omitted
v, so argument omitted
e is positive because money is coming into wallet /
ank gives the lender the money. Borrower must pay
000, but only receives $197,250, so for the adjusted
t use the actual cash that was received: $197,250.
Bank takes a total fee of $200,000 * 1.00% + $750 = $2,750
Borrower must pay back full $200,000, but only receives $197,250

RATE function uses the $197,250 as the present value amount

Present value is positive because money is coming into wallet / purse. The bank gives the lender the money. Borrower must p

-1073.64324602428
 daily 365
monthly 12
quarterly 4

the money. Borrower must pay back full 200,000, but only receives $197,250, so for the adjusted rate you must use the actual cash that w
se the actual cash that was received: $197,250.
Q #10 What is the adjusted APR when there are points and fees for a loan?
Variables and function arguments: Inputs & formulas:
Loan Amount $200,000.00
Annual interest rate = APR 0.05
Years for Loan 30
Periods per Year 12

Points = % of loan amount take as a fee by bank 0.01

Fees = additional fee taken by bank $750.00
Total Fees taken by bank $2,750.00
Amount borrower actually receives $197,250.00
Period rate = rate 0.0041666666667
Total number of periods = nper 360

Monthly loan payment based on original amount

(because that is how much is owed) = pmt -$1,073.64
Adjusted Period Rate 0.0042683797769
Adjusted APR 0.0512205573227

Adjusted period
rate and APR RATE( nper , pmt , pv , [fv] , [type] , [guess] )
reflects the true Period rate
cash flow and
therefore are Payment is negative Annuity is End, so type is omitted
higher than the because money is coming
quoted rates out of wallet / purse. This is No fv, so argument omitted
the monthly payment.
Present value is positive because money is coming into wallet /
purse. The bank gives the lender the money. Borrower must pay
back full 200,000, but only receives $197,250, so for the adjusted
rate you must use the actual cash that was received: $197,250.
 Formulas:
Bank takes a total fee
of $200,000 * 1.00% +
$750 = $2,750

Borrower must
pay back full
=C5*C9+C10 $200,000, but
=C5-C11 only receives
$197,250
=C6/C8
=C7*C8

=PMT(C13,C14,C5) RATE function

uses the
=RATE(C14,C15,C12) $197,250 as the
=C16*C8 present value
amount
, [type] , [guess] )

No guess, so argument omitted

Annuity is End, so type is omitted
v, so argument omitted
e is positive because money is coming into wallet /
ank gives the lender the money. Borrower must pay
000, but only receives $197,250, so for the adjusted
t use the actual cash that was received: $197,250.
Bank takes a total fee of $200,000 * 1.00% + $750 = $2,750
Borrower must pay back full $200,000, but only receives $197,250

RATE function uses the $197,250 as the present value amount

Present value is positive because money is coming into wallet / purse. The bank gives the lender the money. Borrower must p
 daily 365
monthly 12
quarterly 4

the money. Borrower must pay back full 200,000, but only receives $197,250, so for the adjusted rate you must use the actual cash that w
se the actual cash that was received: $197,250.
Q #11 How much to pay for machine with irregular cash flows and equal time periods?
Variables and function arguments: Inputs & formulas:
Asset to buy = C & C Router Machine
Annual discount rate = RRR 0.15
Time period = Year
Cost of machine at time 0 $285,000.00
Estimated cash flows that machine will generate:
Year 1 $100,000.00
Year 2 $90,000.00
Year 3 $85,000.00
Year 4 $75,000.00
Year 5 $70,000.00
Value of cash flows at time zero
NPV at time 0

This is the
max to pay NPV( rate , value1 , value2… )
for
machine!
Period discount Cash flows for each
rate is the required equal time period
rate of return that
the company has
estimated
Check: Formulas:
-$86,956.52 =PV($C$6,ROWS(G$10:G10),,C10)
-$68,052.93 =PV($C$6,ROWS(G$10:G11),,C11)
-$55,888.88 =PV($C$6,ROWS(G$10:G12),,C12)
-$42,881.49 =PV($C$6,ROWS(G$10:G13),,C13)
-$34,802.37 =PV($C$6,ROWS(G$10:G14),,C14)
-$288,582.20 =SUM(G10:G14)
Q #11 How much to pay for machine with irregular cash flows and equal time periods?
Variables and function arguments: Inputs & formulas:
Asset to buy = C & C Router Machine Because the
value of the cash
Annual discount rate = RRR 0.15 flows at time zero
Time period = Year is larger than the
Cost of machine at time 0 $285,000.00 cost, it looks like
a profitable
Estimated cash flows that machine will generate: machine
Year 1 $100,000.00
Year 2 $90,000.00
Year 3 $85,000.00
Year 4 $75,000.00
Year 5 $70,000.00
Value of cash flows at time zero $288,582.20 =NPV(C6,C10:C14)
NPV at time 0 $3,582.20 =C15-C8

This is the
max to pay NPV( rate , value1 , value2… ) Value - Cost =
for $288,582.20 -
machine! $285,000 =
Period discount Cash flows for each 3,582.20
rate is the required equal time period
rate of return that
the company has
estimated

{=SUM(C10:C14/
Math formula for NPV: $288,582.20 (1+C6)^RIGHT(B10:B14))}
Check: Formulas:
-$86,956.52 =PV($C$6,ROWS(G$10:G10),,C10)
-$68,052.93 =PV($C$6,ROWS(G$10:G11),,C11)
-$55,888.88 =PV($C$6,ROWS(G$10:G12),,C12)
-$42,881.49 =PV($C$6,ROWS(G$10:G13),,C13)
-$34,802.37 =PV($C$6,ROWS(G$10:G14),,C14)
-$288,582.20 =SUM(G10:G14)

Value - Cost = $288,582.20 - $285,000 = 3,582.20

Q #12 How much to pay for machine with irregular cash flows and un-equal time periods?
Variables and function arguments: Inputs & formulas:
Asset to buy = C & C Router Machine
Annual discount rate = RRR 0.15
Estimated cash flows that machine will generate:
Dates Cash Flows
6/01/2022 -$285,000.00
3/01/2023 $100,000.00
3/01/2024 $90,000.00
5/01/2025 $85,000.00
7/01/2026 $75,000.00
7/01/2028 $70,000.00
NPV at time 0

XNPV( rate , values , dates )

Period discount rate is the Cash flows Dates

required rate of return that the associated
company has estimated with cash
flows
flows and un-equal time periods?

Check:
-285000
90074.3924142822
70466.0154905854
56534.5927749192
42375.4909582785
29894.3794959025
4344.87113396771
Formulas:
=C9/(1+$C$6)^((B9-$B$9)/365)
=C10/(1+$C$6)^((B10-$B$9)/365)
=C11/(1+$C$6)^((B11-$B$9)/365)
=C12/(1+$C$6)^((B12-$B$9)/365)
=C13/(1+$C$6)^((B13-$B$9)/365)
=C14/(1+$C$6)^((B14-$B$9)/365)
=SUM(G9:G14)
Q #12 How much to pay for machine with irregular cash flows and un-equal time periods?
Variables and function arguments: Inputs & formulas:
Asset to buy = C & C Router Machine
Annual discount rate = RRR 0.15
Estimated cash flows that machine will generate:
Dates Cash Flows
6/01/2022 -$285,000.00
3/01/2023 $100,000.00
3/01/2024 $90,000.00
5/01/2025 $85,000.00
7/01/2026 $75,000.00
7/01/2028 $70,000.00
NPV at time 0 $4,344.87

XNPV( rate , values , dates )

Period discount rate is the Cash flows Dates

required rate of return that the associated
company has estimated with cash
flows

Math formula for NPV: $4,344.87

flows and un-equal time periods?

Check:
This is the net present value. -285000
This positive value indicates 90074.3924142822
that the machine can bring 70466.0154905854
value to purchaser.
56534.5927749192
42375.4909582785
29894.3794959025
=XNPV(C6,C9:C14,B9:B14) 4344.87113396771

{=SUM(C9:C14/(1+C6)^((B9:B14-B9)/365))}
Formulas:
=C9/(1+$C$6)^((B9-$B$9)/365)
=C10/(1+$C$6)^((B10-$B$9)/365)
=C11/(1+$C$6)^((B11-$B$9)/365)
=C12/(1+$C$6)^((B12-$B$9)/365)
=C13/(1+$C$6)^((B13-$B$9)/365)
=C14/(1+$C$6)^((B14-$B$9)/365)
=SUM(G9:G14)
How much will $25,000 invested today be worth in 15 years at an APR of 6.75%, compounded daily?
n 15 years at an APR of 6.75%, compounded daily?
daily 365
monthly 12
quarterly 4
yearly 1
How much will $25,000 invested today be worth in 15 years at an APR of 6.75%, compounded daily?
Variables and function arguments: Inputs & formulas:
Amount to invest today = pv $25,000.00
Annual interest rate = APR 0.0675
Years 15
Compounding periods per year 365
Period rate = rate 0.000184931506849315
Total number of periods = nper 5475
Future value = fv $68,805.40

Math formula fv: $68,805.40

n 15 years at an APR of 6.75%, compounded daily?
Formulas:

=C6/C8
=C7*C8
=FV(C9,C10,,-C5)

=C5*(1+C9)^C10
daily 365
monthly 12
quarterly 4
yearly 1
If you invest $25 at the end of each month with an estimated APR of 12.00%, how much will you have in 35 years, if the
What is the total amount invested?
What is the total return?
ed APR of 12.00%, how much will you have in 35 years, if the starting balance in the account is $10,000?
If you invest $25 at the end of each month with an estimated APR of 12.00%, how much will you have in 35 years, if the
Variables and function arguments: Inputs & formulas:
Amount in stock account at time 0 = pv $10,000.00
Amount to invest each month $25.00
Annual interest rate = APR 0.12
Years 35
Compounding periods per year 12
Type of annuity: End = 0 or omit, Begin = 1
Period rate = rate 0.01
Total number of periods = nper 420
Future value = fv $813,869.93
Total invested $20,500.00
Total return gained $793,369.93

Math formula fv: $813,869.93

ed APR of 12.00%, how much will you have in 35 years, if the starting balance in the account is $10,000?
Formulas:

=C7/C9
=C8*C9
=FV(C11,C12,-C6,-C5)
=C6*C12+C5
=C13-C14

=C6*(((1+C11)^C12-1)/C11)+C5*(1+C11)^C12
daily 365

monthly 12
quarterly 4
yearly 1
How much should you deposit today to have $100,000 in 15 years, at an APR of 12.50%, compounded quarterly?
0 in 15 years, at an APR of 12.50%, compounded quarterly?
daily 365
monthly 12
quarterly 4
yearly 1
How much should you deposit today to have $100,000 in 15 years, at an APR of 12.50%, compounded quarterly?
Variables and function arguments: Inputs & formulas:
Amount needed in 15 years = fv $100,000.00
Annual interest rate = APR 0.125
Years 15
Compounding periods per year 4
Period rate = rate 0.03125
Total number of periods = nper 60
Present value = pv -$15,782.01

Math formula pv: -$15,782.01

0 in 15 years, at an APR of 12.50%, compounded quarterly?
Formulas:

=C6/C8
=C7*C8
=PV(C9,C10,,C5)

=-C5/(1+C9)^C10
daily 365
monthly 12
quarterly 4
yearly 1
How much should you deposit today if the APR is 5,00% and you want to withdraw $5,000 at the beginning of each mon
What is the total withdrawn over 30 years?
What is the total return earned?
draw $5,000 at the beginning of each month for the next 30 years, at an APR of 5.00%?
daily 365
monthly 12
quarterly 4
yearly 1
How much should you deposit today if the APR is 5,00% and you want to withdraw $5,000 at the beginning of each mon
Variables and function arguments: Inputs & formulas:
Withdrawal amount at the beginning of each month = pmt $5,000.00
Annual interest rate = APR 0.05
Years 30
Compounding periods per year 12
Type of annuity: End = 0 or omit, Begin = 1 1
Period rate = rate 0.00416666666666667
Total number of periods = nper 360
Present value = pv -$935,288.95
Total withdrawn over 30 years $1,800,000.00
Total return earned $864,711.05

Math formula for pv: -$935,288.95

draw $5,000 at the beginning of each month for the next 30 years, at an APR of 5.00%?
Formulas:

=C6/C8
=C7*C8
=PV(C10,C11,C5,,C9)
=C11*C5
=C13+C12

=-C5*((1-(1+C10)^-(C11-1))/C10)-C5
daily 365
monthly 12
quarterly 4
yearly 1
What is the monthly payment for a 5-year, $40,000 car loan with a $10,000 balloon payment and a 3.75% APR?
oan with a $10,000 balloon payment and a 3.75% APR?
daily 365
monthly 12

quarterly 4
yearly 1
What is the monthly payment for a 5-year, $40,000 car loan with a $10,000 balloon payment and a 3.75% APR?
Variables and function arguments: Inputs & formulas:
Loan amount = price - down payment = pv $40,000.00
Balloon payment at end = fv $10,000.00
Annual interest rate = APR 0.0375
Years 5
Compounding periods per year 12
Type of annuity: End = omit
Period rate = rate 0.003125
Total number of periods = nper 60
Monthly car payment = pmt -$580.37

Math formula for pmt: $580.37

oan with a $10,000 balloon payment and a 3.75% APR?
Formulas:

=C7/C9
=C9*C8
=PMT(C11,C12,C5,-C6)

=C5/((1-(1+C11)^-C12)/C11)-C6/(((1+C11)^C12-1)/C11)
daily 365

quarterly 4
yearly 1
How long to pay off a credit card bill if you make only the minimum payment ($75) if the balance is $5,000, at an APR of
minimum payment ($75) if the balance is $5,000, at an APR of 16.75%?
How long to pay off a credit card bill if you make only the minimum payment ($75) if the balance is $5,000, at an APR of
Variables and function arguments: Inputs & formulas:
Balance on credit card = pv $5,000.00
Annual interest rate = APR 0.1675
Compounding periods per year 12
Minimum monthly payment = pmt $75.00
Type of annuity: End = omit
Period rate = rate 0.01395833333333
Total number of periods = nper 192.415538906298
Years 16.0346282421915

Math formula for nper: 192.415538906298

minimum payment ($75) if the balance is $5,000, at an APR of 16.75%?
Formulas:

=C6/C7
=NPER(C10,-C8,C5)
=C11/C7

=LOG(-(1/(C5/C8*C10-1)),1+C10)
daily 365
monthly 12
quarterly 4
What is the maximum you should pay for machine with end-of-the-year cash flows $90,000, $100,000, $90,000, $80,0
required rate of return of 17.00%? If the cost of the machine is $310,000, what is the net present v
s $90,000, $100,000, $90,000, $80,000, and $125,000 and a
$310,000, what is the net present value?
 What is the maximum you should pay for machine with end-of-the-year cash flows $90,000, $100,000, $90,000, $80,0
required rate of return of 17.00%? If the cost of the machine is $310,000, what is the net present v
Variables and function arguments: Inputs & formulas:
Asset to buy = C & C Router Machine
Annual discount rate = RRR 0.17
Time period = Year
Cost of machine at time 0 $310,000.00
Estimated cash flows that machine will generate:
Year 1 $90,000.00
Year 2 $100,000.00
Year 3 $90,000.00
Year 4 $80,000.00
Year 5 $125,000.00
Value of cash flows at time zero $305,873.68 =NPV(C6,C10:C14)
NPV at time 0 -$4,126.32 =C15-C8

{=SUM(C10:C14/
Math formula for NPV: $305,873.68 (1+C6)^RIGHT(B10:B14))}
s $90,000, $100,000, $90,000, $80,000, and $125,000 and a
$310,000, what is the net present value?

Check: Formulas:
-$76,923.08 =PV($C$6,ROWS(F$10:F10),,C10)
-$73,051.36 =PV($C$6,ROWS(F$10:F11),,C11)
-$56,193.35 =PV($C$6,ROWS(F$10:F12),,C12)
-$42,692.00 =PV($C$6,ROWS(F$10:F13),,C13)
-$57,013.89 =PV($C$6,ROWS(F$10:F14),,C14)
-$305,873.68 =SUM(F10:F14)
 What is the maximum you should pay for a machine with scheduled cash flows of $90,000, $100,000, $90,000, $80
$125,000 that occur on the following dates: 3/1/23, 3/1/24, 5/1/25, 7/1/26, and 7/1/28, with a required rate of retur
and a machine cost of $310,000 incurred on 6/1/22? What is the net present value?
chine with scheduled cash flows of $90,000, $100,000, $90,000, $80,000, and
23, 3/1/24, 5/1/25, 7/1/26, and 7/1/28, with a required rate of return of 17.00%
0,000 incurred on 6/1/22? What is the net present value?
 What is the maximum you should pay for a machine with scheduled cash flows of $90,000, $100,000, $90,000, $80
$125,000 that occur on the following dates: 3/1/23, 3/1/24, 5/1/25, 7/1/26, and 7/1/28, with a required rate of retur
and a machine cost of $310,000 incurred on 6/1/22? What is the net present value?
Variables and function arguments: Inputs & formulas:
Asset to buy = C & C Router Machine
Annual discount rate = RRR 0.17
Estimated cash flows that machine will generate:
Dates Cash Flows
6/01/2022 -$310,000.00
3/01/2023 $90,000.00
3/01/2024 $100,000.00
5/01/2025 $90,000.00
7/01/2026 $80,000.00
7/01/2028 $125,000.00
NPV at time 0 -$6,890.87

Math formula for NPV: -$6,890.87

chine with scheduled cash flows of $90,000, $100,000, $90,000, $80,000, and
23, 3/1/24, 5/1/25, 7/1/26, and 7/1/28, with a required rate of return of 17.00%
0,000 incurred on 6/1/22? What is the net present value?

Check:
-310000
80028.2317396244
75967.5358194444
56923.1915022293
42126.50329095
48063.6672097764
=XNPV(C6,C9:C14,B9:B14) -6890.8704379756

{=SUM(C9:C14/(1+C6)^((B9:B14-B9)/365))}
Formulas:
=C9/(1+$C$6)^((B9-$B$9)/365)
=C10/(1+$C$6)^((B10-$B$9)/365)
=C11/(1+$C$6)^((B11-$B$9)/365)
=C12/(1+$C$6)^((B12-$B$9)/365)
=C13/(1+$C$6)^((B13-$B$9)/365)
=C14/(1+$C$6)^((B14-$B$9)/365)
=SUM(G9:G14)
What is the interest that is taken out of your home mortgage payment each month? To answer this, create an amortizati

Inputs &
Variables and function arguments: formulas: Formulas:
Amount of home mortgage loan = pv $525,000.00
Annual interest rate = APR 0.04
Years 30
Compounding periods per year 12
Type of annuity: End = omit
Period rate = rate
Total number of periods = nper
Monthly mortgage payment = pmt

Amortization Table
Month PMT Interest to bank Reduction on loan
0
1
2
3
4
5
6
7
8
9
10
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
answer this, create an amortization table for a 30-year $525,000 home mortgage loan at 4% APR.

Balance
daily 365
monthly 12
quarterly 4
yearly 1

Interest for month 1 is the balance from previous month*period rate = $0.00*0.00
Interest for month 240 is the balance from previous month*period rate = $0.00*0
th*period rate = $0.00*0.0000% = $0.00
onth*period rate = $0.00*0.0000% = $0.00
What is the interest that is taken out of your home mortgage payment each month? To answer this, create an amortizati

Inputs &
Variables and function arguments: formulas: Formulas:
Amount of home mortgage loan = pv $525,000.00
Annual interest rate = APR 0.04
Years 30
Compounding periods per year 12
Type of annuity: End = omit
Period rate = rate 0.00333333333 =C6/C8
Total number of periods = nper 360 =C7*C8
Monthly mortgage payment = pmt -$2,506.43 =PMT(C10,C11,C5)
Formulas manually copied down:
Formula in cell C23: =-$C$12
Formula in cell D23: =F22*C$10
Formula in cell E23: =C23-D23 Formula in just 1 cell:
Formula in cell F23: =F22-E23 Formula in cell F22: =C5
Amortization Table
Month PMT Interest to bank Reduction on loan
0
1 $2,506.43 $1,750.00 $756.43
2 $2,506.43 $1,747.48 $758.95
3 $2,506.43 $1,744.95 $761.48
4 $2,506.43 $1,742.41 $764.02
5 $2,506.43 $1,739.86 $766.57
6 $2,506.43 $1,737.31 $769.12
7 $2,506.43 $1,734.74 $771.69
8 $2,506.43 $1,732.17 $774.26
9 $2,506.43 $1,729.59 $776.84
10 $2,506.43 $1,727.00 $779.43
230 $2,506.43 $885.63 $1,620.80
231 $2,506.43 $880.23 $1,626.20
232 $2,506.43 $874.81 $1,631.62
233 $2,506.43 $869.37 $1,637.06
234 $2,506.43 $863.91 $1,642.52
235 $2,506.43 $858.44 $1,647.99
236 $2,506.43 $852.94 $1,653.49
237 $2,506.43 $847.43 $1,659.00
238 $2,506.43 $841.90 $1,664.53
239 $2,506.43 $836.35 $1,670.08
240 $2,506.43 $830.79 $1,675.64
241 $2,506.43 $825.20 $1,681.23
242 $2,506.43 $819.60 $1,686.83
243 $2,506.43 $813.97 $1,692.46
244 $2,506.43 $808.33 $1,698.10
245 $2,506.43 $802.67 $1,703.76
246 $2,506.43 $796.99 $1,709.44
247 $2,506.43 $791.30 $1,715.13
248 $2,506.43 $785.58 $1,720.85
249 $2,506.43 $779.84 $1,726.59
250 $2,506.43 $774.09 $1,732.34
251 $2,506.43 $768.31 $1,738.12
252 $2,506.43 $762.52 $1,743.91
253 $2,506.43 $756.71 $1,749.72
254 $2,506.43 $750.87 $1,755.56
255 $2,506.43 $745.02 $1,761.41
256 $2,506.43 $739.15 $1,767.28
257 $2,506.43 $733.26 $1,773.17
258 $2,506.43 $727.35 $1,779.08
259 $2,506.43 $721.42 $1,785.01
260 $2,506.43 $715.47 $1,790.96
261 $2,506.43 $709.50 $1,796.93
262 $2,506.43 $703.51 $1,802.92
263 $2,506.43 $697.50 $1,808.93
264 $2,506.43 $691.47 $1,814.96
265 $2,506.43 $685.42 $1,821.01
266 $2,506.43 $679.35 $1,827.08
267 $2,506.43 $673.26 $1,833.17
268 $2,506.43 $667.15 $1,839.28
269 $2,506.43 $661.02 $1,845.41
270 $2,506.43 $654.87 $1,851.56
271 $2,506.43 $648.69 $1,857.74
272 $2,506.43 $642.50 $1,863.93
273 $2,506.43 $636.29 $1,870.14
274 $2,506.43 $630.06 $1,876.38
275 $2,506.43 $623.80 $1,882.63
276 $2,506.43 $617.53 $1,888.91
277 $2,506.43 $611.23 $1,895.20
278 $2,506.43 $604.91 $1,901.52
279 $2,506.43 $598.57 $1,907.86
280 $2,506.43 $592.21 $1,914.22
281 $2,506.43 $585.83 $1,920.60
282 $2,506.43 $579.43 $1,927.00
283 $2,506.43 $573.01 $1,933.42
284 $2,506.43 $566.56 $1,939.87
285 $2,506.43 $560.10 $1,946.33
286 $2,506.43 $553.61 $1,952.82
287 $2,506.43 $547.10 $1,959.33
288 $2,506.43 $540.57 $1,965.86
289 $2,506.43 $534.02 $1,972.41
290 $2,506.43 $527.44 $1,978.99
291 $2,506.43 $520.84 $1,985.59
292 $2,506.43 $514.23 $1,992.20
293 $2,506.43 $507.58 $1,998.85
294 $2,506.43 $500.92 $2,005.51
295 $2,506.43 $494.24 $2,012.19
296 $2,506.43 $487.53 $2,018.90
297 $2,506.43 $480.80 $2,025.63
298 $2,506.43 $474.05 $2,032.38
299 $2,506.43 $467.27 $2,039.16
300 $2,506.43 $460.48 $2,045.95
301 $2,506.43 $453.66 $2,052.77
302 $2,506.43 $446.81 $2,059.62
303 $2,506.43 $439.95 $2,066.48
304 $2,506.43 $433.06 $2,073.37
305 $2,506.43 $426.15 $2,080.28
306 $2,506.43 $419.21 $2,087.22
307 $2,506.43 $412.26 $2,094.17
308 $2,506.43 $405.28 $2,101.15
309 $2,506.43 $398.27 $2,108.16
310 $2,506.43 $391.25 $2,115.18
311 $2,506.43 $384.19 $2,122.24
312 $2,506.43 $377.12 $2,129.31
313 $2,506.43 $370.02 $2,136.41
314 $2,506.43 $362.90 $2,143.53
315 $2,506.43 $355.76 $2,150.67
316 $2,506.43 $348.59 $2,157.84
317 $2,506.43 $341.39 $2,165.04
318 $2,506.43 $334.18 $2,172.25
319 $2,506.43 $326.94 $2,179.49
320 $2,506.43 $319.67 $2,186.76
321 $2,506.43 $312.38 $2,194.05
322 $2,506.43 $305.07 $2,201.36
323 $2,506.43 $297.73 $2,208.70
324 $2,506.43 $290.37 $2,216.06
325 $2,506.43 $282.98 $2,223.45
326 $2,506.43 $275.57 $2,230.86
327 $2,506.43 $268.13 $2,238.30
328 $2,506.43 $260.67 $2,245.76
329 $2,506.43 $253.19 $2,253.24
330 $2,506.43 $245.68 $2,260.75
331 $2,506.43 $238.14 $2,268.29
332 $2,506.43 $230.58 $2,275.85
333 $2,506.43 $222.99 $2,283.44
334 $2,506.43 $215.38 $2,291.05
335 $2,506.43 $207.75 $2,298.68
336 $2,506.43 $200.08 $2,306.35
337 $2,506.43 $192.40 $2,314.03
338 $2,506.43 $184.68 $2,321.75
339 $2,506.43 $176.94 $2,329.49
340 $2,506.43 $169.18 $2,337.25
341 $2,506.43 $161.39 $2,345.04
342 $2,506.43 $153.57 $2,352.86
343 $2,506.43 $145.73 $2,360.70
344 $2,506.43 $137.86 $2,368.57
345 $2,506.43 $129.96 $2,376.47
346 $2,506.43 $122.04 $2,384.39
347 $2,506.43 $114.09 $2,392.34
348 $2,506.43 $106.12 $2,400.31
349 $2,506.43 $98.12 $2,408.31
350 $2,506.43 $90.09 $2,416.34
351 $2,506.43 $82.04 $2,424.39
352 $2,506.43 $73.95 $2,432.48
353 $2,506.43 $65.85 $2,440.58
354 $2,506.43 $57.71 $2,448.72
355 $2,506.43 $49.55 $2,456.88
356 $2,506.43 $41.36 $2,465.07
357 $2,506.43 $33.14 $2,473.29
358 $2,506.43 $24.90 $2,481.53
359 $2,506.43 $16.63 $2,489.80
360 $2,506.43 $8.33 $2,498.10
answer this, create an amortization table for a 30-year $525,000 home mortgage loan at 4% APR.

Formula in just 1 cell:

Formula in cell F22: =C5

Balance
$525,000.00
$524,243.57
$523,484.62
$522,723.14
$521,959.12
$521,192.55
$520,423.43
$519,651.74
$518,877.48
$518,100.65
$517,321.22
$264,068.68
$262,442.48
$260,810.86
$259,173.80
$257,531.28
$255,883.29
$254,229.80
$252,570.81
$250,906.28
$249,236.20
$247,560.56
$245,879.33
$244,192.50
$242,500.04
$240,801.95
$239,098.19
$237,388.75
$235,673.62
$233,952.77
$232,226.18
$230,493.84
$228,755.72
$227,011.81
$225,262.08
$223,506.53
$221,745.12
$219,977.84
$218,204.67
$216,425.59
$214,640.57
$212,849.61
$211,052.68
$209,249.76
$207,440.83
$205,625.87
$203,804.86
$201,977.78
$200,144.60
$198,305.32
$196,459.91
$194,608.35
$192,750.61
$190,886.68
$189,016.54
$187,140.17
$185,257.54
$183,368.63
$181,473.43
$179,571.91
$177,664.05
$175,749.84
$173,829.24
$171,902.24
$169,968.82
$168,028.95
$166,082.61
$164,129.79
$162,170.46
$160,204.60
$158,232.19
$156,253.20
$154,267.61
$152,275.40
$150,276.56
$148,271.05
$146,258.86
$144,239.96
$142,214.33
$140,181.94
$138,142.79
$136,096.83
$134,044.06
$131,984.44
$129,917.96
$127,844.59
$125,764.31
$123,677.09
$121,582.92
$119,481.76
$117,373.61
$115,258.42
$113,136.18
$111,006.88
$108,870.47
$106,726.94
$104,576.27
$102,418.42
$100,253.39
$98,081.13
$95,901.64
$93,714.88
$91,520.84
$89,319.48
$87,110.78
$84,894.72
$82,671.27
$80,440.41
$78,202.11
$75,956.36
$73,703.11
$71,442.36
$69,174.07
$66,898.22
$64,614.78
$62,323.74
$60,025.05
$57,718.71
$55,404.67
$53,082.92
$50,753.44
$48,416.18
$46,071.14
$43,718.28
$41,357.58
$38,989.01
$36,612.54
$34,228.15
$31,835.81
$29,435.50
$27,027.19
$24,610.85
$22,186.46
$19,753.98
$17,313.40
$14,864.68
$12,407.80
$9,942.73
$7,469.44
$4,987.91
$2,498.10
$0.00
daily 365
monthly 12
quarterly 4
yearly 1

Interest for month 1 is the balance from previous month*period rate = $525,000.0
Interest for month 240 is the balance from previous month*period rate = $249,23
th*period rate = $525,000.00*0.3333% = $1,750.00
onth*period rate = $249,236.20*0.3333% = $830.79
 What is the net present value for the yearly cash flows $55,000, $10,000, $20,000, $30,000, and $30,000 at a dis
zero of –$100,000? Answer this question is two ways:
• In cell F15, use the NPV function.
• In cell F16, use the XNPV function.
Is there a difference between the results of these two methods?

RRR 0.15 Algorithm that shows individual discounted cash flows and differences bet
Date Year Cash Flow NPV Long Hand Formula:
1/1/2021 Year 0 -100000 -$100,000.00 =D7/(1+$D$4)^RIGHT(C7)
1/1/2022 Year 1 55000 $47,826.09 =D8/(1+$D$4)^RIGHT(C8)
1/1/2023 Year 2 10000 $7,561.44 =D9/(1+$D$4)^RIGHT(C9)
1/1/2024 Year 3 20000 $13,150.32 =D10/(1+$D$4)^RIGHT(C10)
1/1/2025 Year 4 30000 $17,152.60 =D11/(1+$D$4)^RIGHT(C11)
1/1/2026 Year 5 30000 $14,915.30 =D12/(1+$D$4)^RIGHT(C12)
$605.75 =SUM(F7:F12)

NPV
XNPV

𝑋𝑁𝑃𝑉=∑〗
𝑁𝑃𝑉=∑_(𝑗=1)^𝑛▒ 〖𝐶𝐹〗 _𝑗/ 〖 (1+𝑃𝑒𝑟𝑅𝑎𝑡𝑒)
NPV-XNPV Difference
0,000, and $30,000 at a discount rate of 15% and a cost at time
on is two ways:
nction.
unction.
these two methods?

h flows and differences between the two functions: Exponent with Old School formula:
XNPX Longhand Formula: Integer Proportion of Days
-$100,000.00 {=D7:D12/(1+D4)^((B7:B12-B7)/365)} 0 0
$47,826.09 {=D7:D12/(1+D4)^((B7:B12-B7)/365)} 1 1
$7,561.44 {=D7:D12/(1+D4)^((B7:B12-B7)/365)} 2 2
$13,150.32 {=D7:D12/(1+D4)^((B7:B12-B7)/365)} 3 3
$17,146.03 {=D7:D12/(1+D4)^((B7:B12-B7)/365)} 4 4.0027397260274
$14,909.59 {=D7:D12/(1+D4)^((B7:B12-B7)/365)} 5 5.0027397260274
#NAME? =SUM(_xlfn.anchorarray(I7))

4*365: 1460

𝑋𝑁𝑃𝑉=∑_(𝑗=1)^𝑛▒
+𝑃𝑒𝑟𝑅𝑎𝑡𝑒) 〖𝐶𝐹〗 _𝑗/ 〖 (1+𝑃𝑒𝑟𝑅𝑎𝑡𝑒) 〗 ^(((𝐷_𝑗−
〗 ^𝑇𝑜𝑡𝑎𝑙𝑃𝑒𝑟𝑖𝑜𝑑𝑠
 Exponent with New School formula:
Integer Proportion of Days
#NAME? 0
#NAME? 1
#NAME? 2
#NAME? 3
2/29/2024 #NAME? 4.0027397260274 ###
#NAME? 5.0027397260274

=M11*365

〗 ^(((𝐷_𝑗−𝐷_1 ))/365)
 What is the net present value for the yearly cash flows $55,000, $10,000, $20,000, $30,000, and $30,000 at a dis
zero of –$100,000? Answer this question is two ways:
• In cell F15, use the NPV function.
• In cell F16, use the XNPV function.
Is there a difference between the results of these two methods?

RRR 0.15 Algorithm that shows individual discounted cash flows and differences bet
Date Year Cash Flow NPV Long Hand Formula:
1/1/2021 Year 0 -100000 -$100,000.00 =D7/(1+$D$4)^RIGHT(C7)
1/1/2022 Year 1 55000 $47,826.09 =D8/(1+$D$4)^RIGHT(C8)
1/1/2023 Year 2 10000 $7,561.44 =D9/(1+$D$4)^RIGHT(C9)
1/1/2024 Year 3 20000 $13,150.32 =D10/(1+$D$4)^RIGHT(C10)
1/1/2025 Year 4 30000 $17,152.60 =D11/(1+$D$4)^RIGHT(C11)
1/1/2026 Year 5 30000 $14,915.30 =D12/(1+$D$4)^RIGHT(C12)
$605.75 =SUM(F7:F12)

NPV $605.75 =NPV(D4,D8:D12)+D7

XNPV $593.47 =XNPV(D4,D7:D12,B7:B12)

𝑋𝑁𝑃𝑉=∑〗
𝑁𝑃𝑉=∑_(𝑗=1)^𝑛▒ 〖𝐶𝐹〗 _𝑗/ 〖 (1+𝑃𝑒𝑟𝑅𝑎𝑡𝑒)
NPV-XNPV Difference $12.28 =NPV-XNPV
0,000, and $30,000 at a discount rate of 15% and a cost at time
on is two ways:
nction.
unction.
these two methods?

h flows and differences between the two functions: Exponent with Old School formula:
XNPX Longhand Formula: Integer Proportion of Days
-$100,000.00 {=D7:D12/(1+D4)^((B7:B12-B7)/365)} 0 0
$47,826.09 {=D7:D12/(1+D4)^((B7:B12-B7)/365)} 1 1
$7,561.44 {=D7:D12/(1+D4)^((B7:B12-B7)/365)} 2 2
$13,150.32 {=D7:D12/(1+D4)^((B7:B12-B7)/365)} 3 3
$17,146.03 {=D7:D12/(1+D4)^((B7:B12-B7)/365)} 4 4.0027397260274
$14,909.59 {=D7:D12/(1+D4)^((B7:B12-B7)/365)} 5 5.0027397260274
#NAME? =SUM(_xlfn.anchorarray(I7))

4*365: 1460

𝑋𝑁𝑃𝑉=∑_(𝑗=1)^𝑛▒
+𝑃𝑒𝑟𝑅𝑎𝑡𝑒) 〖𝐶𝐹〗 _𝑗/ 〖 (1+𝑃𝑒𝑟𝑅𝑎𝑡𝑒) 〗 ^(((𝐷_𝑗−
〗 ^𝑇𝑜𝑡𝑎𝑙𝑃𝑒𝑟𝑖𝑜𝑑𝑠
 Exponent with New School formula:
Integer Proportion of Days
#NAME? 0
#NAME? 1
#NAME? 2
#NAME? 3
2/29/2024 #NAME? 4.0027397260274 ###
#NAME? 5.0027397260274

=M11*365

〗 ^(((𝐷_𝑗−𝐷_1 ))/365)