Question

Show the excel formulas used and answer all questions

Ex. 1 You have $5,000 in your savings account that pays 4% interest. How much will you have in your account after 20 years, a) if your bank pays annually compounded interest? b) if your bank pays monthly compounded interest? c) if your bank pays daily compounded interest? Current balance Interest Years Compounding Annually Monthly Daily a) FV b) FV c) FV Ex. 2 If you need $10,000 in 7 years and you can earn 6% on your investment, How much do you have to invest today, a) if your investment earns annually compounded interest? b) if your investment earns monthly compounded interest? c) if your investment earns daily compounded interest? Future balance Interest Years Compounding Annually Monthly Daily a) PV b) PV c) PV

Answer 1

S . Book1 - Excel Sign in - 0 x Share Page Layout Formulas Data Review View Help Tell me what you want to do Calibri = General File Home Insert % Cut Copy Paste Format Painter Clipboard D AutoSum" A O 11 VAA A == EE Wrap Text Merge Center X * Insert Delete Format B IU . S E . % 1 D Conditional Format as Cell Formatting Table Styles Styles 809 Clear Sort & Filter Editing Find & Select Font Alignment Number S Cells X for payments. In case if daily compunding, we divide it by Number of days and multiply it in Nper that is Number of payment. F G H I J K A B c L M N O 5,000.00 4% 20 Current Balance Interest Years Future value Compounding 1. Annually 2. Monthly 3. Daily us 10,955.62 11,112.91 11,127.22 Explanation: The formulae we use is FV(Rate, Nper, Pmt,Pv, Type). Where rate is rate of interest, Nper is number of payments, pmt is payment we do in case of no intermittent payment it is zero. Pv is present value that is current balance or amount at the beginning, and in type we select 0 if we have to compound in the beginning of the year or 1 if we have to combine in the beginning. In case of Monthly Compunding we divide rate by Number of Months and Multiply by Number of Months in Nper that is Number of payments. In case if daily compunding, we divide it by Number of days and multiply it in Nper that is Number of payment. Sheet1 +

5 Book1 - Excel Sign in - 0 X Share File fx . Home Insert Page Layout Formulas Data Review View Help 5 ? AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name - Used - Time Reference Trigº Functions Manager Function Library * A . lale e Calculate Now Tell me what you want to do Define Name * Use in Formula Create from Selection Defined Names Insert Function - Trace Precedents 5 Show Formulas Trace Dependents ! Error Checking - Remove Arrows - Evaluate Formula Formula Auditing Watch Window Calculation Calculate Sheet Options Calculation C12 Explanation: The formulae we use is FV(Rate, Nper, Pmt, Pv,Type). Where rate is rate of interest, Nper is number of payments, pmt is payment we do in case of no intermittent 5000 0.04 20 Current Balance Interest Years Future value Compounding 1. Annually 2. Monthly 3. Daily =FV(D4,D5,0,-03,0) =FV(D4/12,05*12,0,-D3,0) =FV(D4/365,05*365,0,-D3,0) Explanation: The formulae we use is FV(Rate,Nper, Pmt, Pv,Type). Where rate is rate of interest, Nper is number of payments, pmt is payment we do in case of no intermittent payment it is zero. Pv is present value that is cut and in type we select O if we have to compound in the beginning of the year or 1 if we have to combine in the beginning. In case of Monthly Compunding we divide rate by Number of Months and Multiply by Number of Mon case if daily compunding, we divide it by Number of days and multiply it in Nper that is Number of payment. Sheet1 + Ready D - + 90%

S . Book1 - Excel Sign in - 0 X Share File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Cut Calibri Us Copy O Accounting X 11 VAA P. A. v * Autosum ab Wrap Text Merge & Center, 1 A Accounting T % , Paste Format Painter B IU . E E EE 8.98 * Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Fill- Clear Sort & Find & Filter Select Editing Clipboard Font Alignment Number Cells x fox E L =PV(B4,B3,0,-82,0) C D B E F G H I J K L M N O P Q R S $ 10,000.00 6% 2 Future Balance 3 Year 4 Rate of Interest 5 Present Value 6 Compounding 7 Annually 8 Monthly 9 Daily 6,650.57 6,577.35 6,570.69 Explanation: We use formulae PV(Rate, Nper,Pmt, Fv, type) where rate is rate of interest, Nper is rate of interest, Pmt is payment if no payment is done it is Zero, Future Value is Future balance that is amount at the end. Type is O if compunding is done at the end of the year or 1 if compounding is done at the beginning of the year. In Monthly case, we Divide rate by number of months and Nper is Multiplied by Number of Months, Similarly, we do this in case of daily. Sheet1 Sheet2 + Ready P - _ _ + 100%

5 Sign in - 0 X Share File fx . Book1 - Excel Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do 5 ? * Use in Formula AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name - Used - - Time Reference Trigº Functions Manager Create from Selection Function Library Defined Names * A . lale Define Name Calculate Now Insert Function - Trace Precedents 5 Show Formulas Trace Dependents ! Error Checking - Watch Remove Arrows - Evaluate Formula Window Formula Auditing Calculation Calculate Sheet Options Calculation E x for =PV(B4,B3,0,-82,0) 110000 10.06 2 Future Balance 3 Year 4 Rate of Interest 5 Present Value 6 Compounding 7 Annually 8 Monthly 9 Daily =PV(B4,B3,0,-82,0) =PV(B4/12,B3*12,0,-82,0) =PV(B4/365,B3*365,0,-B2,0) Explanation : We use formulae PV(Rate,Nper,Pmt, Fv,type) where rate is rate of interest, Nper is rate of interest, Pmt is payment if no payment is done it is Zero, Future Value is the end of the year or 1 if compounding is done at the beginning of the year. In Monthly case, we Divide rate by number of months and Nper is Multiplied by Sheet1 Sheet2 Ready 0 - 1 + 100%