Question

a.   Find the FV of $1,000 invested to earn 10% annually 5 years from now. Answer this question by using a math formula and also by using the Excel function wizard.
	Inputs:	PV =	1000
		I/YR =	10%
		N =	5
	Formula:	FV = PV(1+I)^N =
	Wizard (FV):			$1,610.51
Note: When you use the wizard and fill in the menu items, the result is the formula you see on the formula line if you click on cell E12. Put the pointer on E12 and then click the function wizard (fx) to see the completed menu. Also, it is generally easiest to fill in the wizard menus by clicking on one of the menu slots to activate the cursor and then clicking on the cell where the item is given. Then, hit the tab key to move down to the next menu slot to continue filling out the dialog box.
							Experiment by changing the input values to see how quickly the output values change.
							b.   Now create a table that shows the FV at 0%, 5%, and 20% for 0, 1, 2, 3, 4, and 5 years. Then create a graph with years on the horizontal axis and FV on the vertical axis to display your results.
								Years (D10):	Interest Rate (D9)
									0%	5%	200%
								0	$1,000.00	$1,000.00	$1,000.00
	1	$1,000.00	$1,050.00	$1,200.00
	2	$1,000.00	$1,102.50	$1,400.00
	3	$1,000.00	$1,157.63	$1,728.00
	4	$1,000.00	$1,215.51	$2,073.60
	5	$1,000.00	$1,276.28	$2,488.32
To create the graph, first select the range C33:E38. Then click the chart wizard. Then follow the menu. It is easy to make a chart, but a lot of detailed steps are involved to format it so that it's "pretty." Pretty charts are generally not necessary to get the picture, though. Note that as the last item in the chart menu you are asked if you want to put the chart on the worksheet or on a separate tab. This is a matter of taste. We put the chart below on the spreadsheet so we could see how changes in the data lead to changes in the graph.
							Note that the inputs to the data table, hence to the graph, are now in the row and column heads. Change the 20% in Cell E32 to .3 (or 30%), then to .4, then to .5, etc., to see how the table and the chart changes.
c.   Find the PV of $1,000 due in 5 years if the discount rate is 10% per year. Again, work the problem with a formula and also by using the function wizard.
	Inputs:		FV =	1000
			I/YR   =	10%
			N =	5
	Formula:		PV = FV/(1+I)^N =
	Wizard (PV):
Note: In the wizard's menu, use zero for Pmt because there are no periodic payments. Also, set the FV with a negative sign so that the PV will appear as a positive number.
d.   A security has a cost of $1,000 and will return $2,000 after 5 years. What rate of return does the
security provide?
	Inputs:	PV   =	-1000
		FV   =	2000
		I/YR   =	?
		N =	5
	Wizard (Rate):
Note: Use zero for Pmt since there are no periodic payments. Note that the PV is given a negative sign because it is an outflow (cost to buy the security). Also, note that you must scroll down the menu to complete the inputs.
							e.   Suppose California’s population is 30 million people, and its population is expected to grow by 2% per year. How long would it take for the population to double?
	Inputs:	PV   =		-30
		FV   =		60
		I/YR   = growth rate		2%
		N =		?
	Wizard (NPER):				= Years to double.
f.   Find the PV of an ordinary annuity that pays $1,000 at the end of each of the next 5 years if the interest rate is 15%. Then find the FV of that same annuity.
Inputs:		PMT =		$      1,000
		N =		5
		I/YR =		15%
PV: Use function wizard (PV)			PV   =
FV: Use function wizard (FV)			FV   =
g.   How would the PV and FV of the above annuity change if it were an annuity due rather than an
ordinary annuity?
For the PV, each payment would be received one period sooner, hence would be discounted back one less year. This would make the PV larger. We can find the PV of the annuity due by finding the PV of an ordinary annuity and then multiplying it by (1 + I).
							PV annuity due =			x		=
Exactly the same adjustment is made to find the FV of the annuity due.
FV annuity due =			x		=
h.   What would the FV and the PV for parts a and c be if the interest rate were 10% with
semiannual compounding rather than 10% with annual compounding?
Part a. FV with semiannual compounding:					Orig. Inputs	New Inputs
		Inputs:	PV =		1000	1000
			I/YR   =		10%	5%
			N =		5	10
		Formula:	FV = PV(1+I)^N =
		Wizard (FV):
Part c. PV with semiannual compounding:					Orig. Inputs	New Inputs
		Inputs:	FV =		1000	1000
			I/YR   =		10%	5%
			N =		5	10
		Formula:	PV = FV/(1+I)^N =
		Wizard (PV):
i.   Find the PV and FV of an investment that makes the following end-of-year payments. The
interest rate is 8%.
Year		Payment
1		100
2		200
3		400
	Rate   =	8%
To find the PV, use the NPV function:			PV =
Excel does not have a function for the sum of the future values for a set of uneven payments. Therefore, we must find this FV by some other method. Probably the easiest procedure is to simply compound each payment, then sum them, as is done below. Note that since the payments are received at the end of each year, the first payment is compounded for 2 years, the second for 1 year, and the third for 0 years.
								Year	Payment	x	(1 + I )^(N-t)	=	FV
								1	100		1.17		116.64
								2	200		1.08		216.00
	3	400		1.00		400.00
					Sum =
An alternative procedure for finding the FV would be to find the PV of the series using the NPV
function, then compound that amount, as is done below:
	PV =
	FV of PV =
j.   Suppose you bought a house and took out a mortgage for $50,000. The interest rate is 8%, and   you must amortize the loan over 10 years with equal end-of-year payments. Set up an amortization schedule that shows the annual payments and the amount of each payment that repays the principal and the amount that constitutes interest expense to the borrower and interest income to the lender.
							Original amount of mortgage:			50000
							Term of mortgage:			10
							Interest rate:			0.08
Annual payment (use PMT function):
Year	Beg. Amt.	Pmt	Interest	Principal	End. Bal.
1
2
3
4
5
6
7
8
9
10
	(1) Create a graph that shows how the payments are divided between interest and
	principal repayment over time.
	Go back to cells D184 and D185, and change the interest rate and the term to maturity to see how the payments would change.
	(2) Suppose the loan called for 10 years of monthly payments, 120 payments in all, with
	the same original amount and the same nominal interest rate. What would the
	amortization schedule show now?
	Now we would have a 12 × 10 = 120-payment loan at a monthly rate of .08/12 = 0.666667%.
	The monthly payment would be:
	Month	Beg. Amt.	Pmt	Interest	Principal	End. Bal.
	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

Answer 1

TOO LENGHTHY QUESTION

HAVE TRIED TO GIVE AS MANY POSSIBLE.

EVEN HOMEWORKLIB POLICY WE CAN GIVE MAX 4-5 ANSWERS

SO PLEASE POST REMAINING QUESTIONS AGAIN

ANY DOUBTS IN SUM, PLEASE FEEL FREE TO ASK, HAPPY TO HELP YOU

SEE IMAGE

PV, FV, ANNUITY Microsoft Excel Home Page Layout Formlas Data Review View dd-Ins Trace PrecedentsShow Formulas Define Name Use in Formula- Create from Selection Calculate Now "E Trace Dependents Error Checking Evaluate Formula Window Options Watch | Calculation Calculate Sheet Insert Autosum Recently Financial Logical Text Date &Lookup & Math More Function Used Time Reference &Trig Functions Manager Remove Arrows Function Library Defined Names Formula Auditing Calculation FV Function Arguments PV 1000 10% Rate 110% 4 Nper 5 number 1000 number - 1610.51 6 MATH FV= PV (1+1)AN EXCEL FORMULA P -1000 7 FORMULA FV 1000 (1+0.10)AS FV -1000 (1.61051) FV 1610.51 FV- 5,,-1000) Returns the future value of an investment based on periodic, constant payments and a constant interest rate. Pv is the present vaue, or the lump-sum amount that a series of future 9 10 payments is worth now. If omitted, Pv = 0. Formula result-1,610.51 12 13 14 15 16 17 18 19 i1 1 커| CALCULATOR ( LO AN OPTIONS | pv, fv, ann 15-05-2018

PV, FV, ANNUITY Microsoft Excel Home Page Layout FormulasData Review View dd-Ins Define Name PUse in Formula Trace Precedents t Trace Dependents Remove Arrows Show Formulas Calculate Now - 曙 ◆ Error Checking Insert Autosum Recently Financial Logical Tet Date & Lookup & Math More Function Watch Calculation Calculate Sheet wndow ? Create from Selection Defined Names Used | Evaluate Formula Time Reference ▼ & Tng Functions ▼ Manager options . Function Library Formula Auditing Calculation t =FV(1096,5,,-1000) PV 1000 10% 4 6 MATH FV= PV (1+1)AN EXCEL FORMULA 7 FORMULA FV 1000 (1+0.10)AS $ 1,610.51 FV -1000 (1.61051) FV 1610.51 FV- 9 10 12 13 14 15 16 17 18 19 i1 1 커| CALCULATOR LO AN OPTIONS | pv, fv, ann 福 130% 15-05-2018

PV, FV, ANNUITY Microsoft Excel Home Page Layout FormulasData Review View dd-Ins Define Name PUse in Formula Trace Precedents Show Formulas Trace Dependents Error Checking Remove Arrows Calculate Now Watch Calculation Calculate Sheet Insert Autosum Recently Financial Logical Tet Date & Lookup & Math More Function | Evaluate Formula ? Create from Selection Defined Names wndow options . Used Time Reterence &Tnig Functions Manager Calculation Formula Auditing Function Library 12 13 (b) 14 15 16 17 18 19 20 21 INTEREST RATE 20% 0 $ 1,000.00 $1,000.00 $ 1,000.00 1 $ 1,000.00 $1,050.00 $1,200.00 2 $ 1,000.00 $1,102.50 $ 1,440.00 3 $ 1,000.00 $1,157.63 $1,728.00 4 $ 1,000.00 $1,215.51 $ 2,073.60 5 $ 1,000.00 $1,276.28 $2,488.32 0% 5% 3000 2500 2000 Series1 + Se ries 2 1500 Series3 1000 Series4 Linear (Series1) 23 24 25 26 27 28 29 14 1 r i- CALCULATOR 500 500 LOAN OPTİONS | pv, fv, ann 130% 16-05-2018

PV, FV, ANNUITY Microsoft Excel Home Page Layout Formlas Data Review View dd-Ins Define Name Use in Formula- Create from Selection Trace PrecedentsShow Formulas "E Trace Dependents ф Error Checking Calculate Now Insert Autosum Recently Financial Logical Text Date &Lookup & Math More Function Watch CalculationCalculate Sheet Name Remove Arrows Evaluate Formula Window Options Used Time Reference &Trig Functions Manager Function Library Defined Names Formula Auditing Calculation Function Arguments 29 30 (c) 31 32 1000 10% FV- PV Rate 110% 0.1 Nper 5 -number Fy 1000 塚! --1000 34 35 MATH 36 FORMULA PV 1000/1.61051 37 38 39 40 41 42 43 PV FV/(1+I)AN PV- 1000/(1+0.10)A5 number EXCEL FORMULA PV= 620.9213231 5,,-1000)trns the present value of an investment: the total amount that a series of future payments is worth now. PV 620.92 Fv is the future value, or a cash balance you want to attain after the last payment is made. Formula result-620.9213231 45 46 47 i1 1 커| CALCULATOR LO AN OPTIONS | pv, fv, ann 15-05-2018

PV, FV, ANNUITY Microsoft Excel Home Page Layout FormulasData Review View dd-Ins Trace Precedents Show Formulas Trace Dependents Error Checking Remove Arrows Calculate Now Define Name PUse in Formula Watch CalculationCalculate Sheet Insert Autosum Recently Financial Logical Tet Date & Lookup & Math More Function Evaluate Formula WindowOptions ? Create from Selection Defined Names Time Reterence &Tnig Functions Manager Calculation Used Formula Auditing Function Library E37 29 30 (c) 31 32 1000 10% FV- PV FV/(1+I)AN PV- 1000/(1+0.10)A5 34 35 MATH 36 FORMULA PV 1000/1.61051 37 38 39 40 41 42 43 EXCEL FORMULA PV- 620.92 PV 620.92 45 46 47 i1 1 커| CALCULATOR LO AN OPTIONS | pv, fv, ann 福 130% 15-05-2018

PV, FV, ANNUITY Microsoft Excel Home Page Layout Formlas Data Review View dd-Ins Trace PrecedentsShow Formulas Define Name Use in Formula- Create from Selection Calculate Now Watch CalculationCalculate Sheet "E Trace Dependents Insert Autosum Recently Financial Logical Text Date &Lookup & Math More Function Error Checking Evaluate Formula Window Options Used Time Reference &Trig Functions Manager Remove Arrows Function Library Defined Names Formula Auditing Calculation RATE -x RATE(5,, 1000,2000) 38 39 (d) 40 41 42 43 Function Arguments PV= 1000 2000 FV= Nper 5 00,2000) -number = -1000 2000 number -0.148698355 Pv 1000 F 2000 Type 45 46 47 48 49 50 51 52 53 54 Returns the interest rate per penod of a loan or an nvestment. For example, use 6%/4 for quarterly payments at6% APR. Fv is the future value, or a cash balance you want to attain after the last payment is made. If omitted, uses Fv 0. Formua resut- 0.148698355 56 i1 1 커| CALCULATOR ( LO AN OPTIONS | pv, fv, ann 20:10 15-05-2018

PV, FV, ANNUITY Microsoft Excel Home nert Page Layout Formulas Data Review View dd-Ins s Cut ta copy." Σ AutoSum ー E " ゴWrap Text Sort &Find & в 1 프 . Ej-., Δ. : rーー 逻锂函Merge & Center. $, % , 弼,8 C Conditional Format CeInsert Delete Format Formatting as Table Styles2 Clear Format Painter Cells Edting Number Alignment Clipboard B45 38 39 (d) 40 41 42 43 1000 2000 14.87% PV= FV= 45 46 47 48 49 50 51 52 53 54 56 ( LO | pv, fv, ann i1 1 커| CALCULATOR AN OPTIONS 130% 20:11 15-05-2018

PV, FV, ANNUITY Microsoft Excel Home Page Layout Formlas Data Review View dd-Ins Trace PrecedentsShow Formulas Define Name Use in Formula- Create from Selection Calculate Now "E Trace Dependents Error Checking Evaluate Formula Window Options Watch | Calculation Calculate Sheet Insert Autosum Recently Financial Logical Text Date &Lookup & Math More Function Used Time Reference &Trig Functions Manager Remove Arrows Function Library Defined Names Formula Auditing Calculation NPER Function Arguments 45 (e) 46 47 48 49 50 51 52 53 54 PV= 30 60 2.00% FV= Rate 2% E, = 0.02 = number 6,,-30,60) YEARS TO DOUBLE Pv -30 Fv 60 60 number - 35.00278878 Returns the number of periods for an investment based on periodic, constant payments and a constant interest Fv is the future value, or a cash balance you want to attain after the last payment is made. If omitted, zero is used Formula result35.00278878 56 57 58 59 60 61 62 i1 1 커| CALCULATOR ( LO AN OPTIONS | pv, fv, ann 15-05-2018

PV, FV, ANNUITY Microsoft Excel Home nert Page Layout Formulas Data Review View dd-Ins Cut Σ AutoSum ー E " ゴWrap Text ta copy ▼ в 1 프 . Ej-., Δ. : rーー 逻锂函Merge & Center. $, % , 弼鼎 Conditional Format CeInsert Delete Format Formatting as Table Styles2 Clear Sort &Find & Format Painter Clipboard Alignment Number Cells Edting 45 (e) 46 47 48 49 50 51 52 53 54 PV= 30 60 2.00% FV- 35.00 YEARS TO DOUBLE 56 57 58 59 60 61 62 i1 1 커| CALCULATOR ( LO AN OPTIONS | pv, fv, ann 福 130% 15-05-2018

PV, FV, ANNUITY Microsoft Excel Home Page Layout Formlas Data Review View dd-Ins Trace PrecedentsShow Formulas Define Name Use in Formula- Create from Selection Calculate Now Calculate Sheet "E Trace Dependents Error Checking Evaluate Formula Window Options Watch | Calculation Insert Autosum Recently Financial Logical Text Date &Lookup & Math More Function Used Time Reference &Trig Functions Manager Remove Arrows Function Library Defined Names Formula Auditing Calculation PV 50 51 (f) 52 53 Function Arguments PMT= 1000 0.15 Rate 115% Nper 5 Pmt 1000 15% =-1000 number - number - 3352. 155098 ー PV ,5,-1000) PV OF ANNUITY 56 57 58 59 60 61 62 63 64 65 Returns the present value of an investment: the total amount that a series of future payments is worth now. Pmt s the payment made each period and cannot change over the life of the Formula result3352. 155098 67 i1 1 커| CALCULATOR ( LO AN OPTIONS | pv, fv, ann 15-05-2018

PV, FV, ANNUITY Microsoft Excel Home Page Layout Formlas Data Review View dd-Ins Define Name Use in Formula- Create from Selection Trace PrecedentsShow Formulas "E Trace Dependents φ Error Checking Calculate Now Calculate Sheet Watch | Calculation Insert Autosum Recently Financial Logical Text Date &Lookup & Math More Function Evaluate Formula Window Options Used Time Reference &Trig Functions Manager Remove Arrows Function Library Defined Names Formula Auditing Calculation FV 50 51 (f) 52 53 PMT= 1000 Function Arguments 0.15 Rate 15% Nper 5 Pmt -1000l 15% -1000 PV= $ 3,352.16 PV OF ANNUITY 56 57 58 59 60 61 62 63 64 65 Type number -6742.38125 FV- 5,-1000) Returns the future value of an investment based on periodic, constant payments and a constant interest rate. (FV OF ANNUITY) is the payment made each period; it cannot change over the ife of the Pmt Formula result- 6742 33125 OK 67 i1 1 커| CALCULATOR ( LO AN OPTIONS | pv, fv, ann 15-05-2018

PV, FV, ANNUITY Microsoft Excel Home Page Layout FormulasData Review View dd-Ins Define Name PUse in Formula Trace Precedents Show Formulas Trace Dependents Error Checking Remove Arrows Calculate Now Watch Calculation Calculate Sheet Insert Autosum Recently Financial Logical Tet Date & Lookup & Math More Function ? Create from Selection Defined Names Used Evaluate Formula WindowOptions Time Reference ▼ & Tng Functions ▼ Manager Function Library Formula Auditing Calculation 50 51 (f) 52 53 54 PMT= 1000 15% PV= $ 3,352.16 PV OF ANNUITY 56 57 58 59 60 61 62 63 64 65 FV= 6,742.38 (FV OF ANNUITY) 67 i1 1 커| CALCULATOR LO AN OPTIONS | pv, fv, ann 福 130% 15-05-2018

PV, FV, ANNUITY Microsoft Excel Home Page Layout Formlas Data Review View dd-Ins Define Name Use in Formula- Create from Selection Trace PrecedentsShow Formulas "E Trace Dependents ф Error Checking Calculate Now Insert Autosum Recently Financial Logical Text Date &Lookup & Math More Function Evaluate Formula Window Options Watch CalculationCalculate Sheet Used Time Reference &Trig Functions Manager Remove Arrows Function Library Defined Names Formula Auditing Calculation 62 (g) 63 64 65 PMT- 1000 Function Arguments PV 15% = 0.15 Rate 15% Nper 5 Pmt 1000 PVANNUITY DUE- I-1000, 1) 1000 number 67 68 69 70 71 72 73 74 75 76 Type -3854.978363 Retuns the present value of an investment: the total amount that a series of future payments is worth now Type is a logical value: payment at the beginning of the period 1; payment at the end of the period 0 or omitted. Formua resut 3854.978363 78 79 80 i1 1 커| CALCULATOR ( LOAN OPTIONS | pvt fv, ann 15-05-2018

PV, FV, ANNUITY Microsoft Excel Home Page Layout Formlas Data Review View dd-Ins Define Name Use in Formula- Create from Selection Trace PrecedentsShow Formulas "E Trace Dependents φ Error Checking Calculate Now Insert Autosum Recently Financial Logical Text Date &Lookup & Math More Function Evaluate Formula Window Options Watch CalculationCalculate Sheet Used Time Reference &Trig Functions Manager Remove Arrows Function Library Defined Names Formula Auditing Calculation FV 62 (g) 63 64 65 PMT= 1000 Function Arguments FV 15% 0.15 Rate 1,5% Nper 5 Pmt -1000 鬮-5 PV ANNUITY DUE$3,854.98 !--1000 67 68 69 70 71 72 73 74 75 76 number FV ANNUITY DUE -1000,,1) Type 7753.738437 Returns the future valuc of an investment based on periodic, constant payments and a constant interest rate Type isa value representing the timng of payment: payment at the beginning of the period 1; payment at the end of the period 0 or omitted. Formula result 7753.738437 OK 78 79 80 i1 1 커| CALCULATOR ( LOAN OPTIONS | pvt fv, ann 15-05-2018

PV, FV, ANNUITY Microsoft Excel Home Page Layout FormulasData Review View dd-Ins Define Name PUse in Formula Trace Precedents t Trace Dependents Remove Arrows Show Formulas Calculate Now - 曙 ◆ Error Checking Insert Autosum Recently Financial Logical Tet Date & Lookup & Math More Function Watch Calculation Calculate Sheet wndow ? Create from Selection Defined Names Used Time Reterence &Tnig Functions Manager | Evaluate Formula options . Function Library Formula Auditing Calculation 870 62 (g) 63 64 65 PMT= 1000 15% PV ANNUITY DUE$3,854.98 67 68 69 70 71 72 73 74 75 76 FV ANNUITY DUE= $ 7,753.74 78 79 80 i1 1 커| CALCULATOR ( LO AN OPTIONS | pv, fv, ann 福 130% 15-05-2018