Chapter 7 - Spreadsheets: Financial Functions



1.10 Financial FunctionsPeople often use spreadsheets to track a variety of financial information, such as the value of investment portfolios, loan obligations, income, and expenditures. Money is earned on sums invested in savings accounts, certificates of deposit (CD’s), and money market funds. Borrowers pay for the use of money they have borrowed for school loans, mortgages, car payments, or credit card purchases. This charge for money is called interest. Usually this fee is given as a rate of interest which is then is multiplied by the principal value to calculate the interest fee amount. The principal is the current value of the financial instrument, either a loan or investment. In a finance course, how these interest rates are set is of major import, as well as understanding the time value of money (what you expect to be paid for use of your money) and risk (the uncertainty of getting this money back from the borrower).In this class we will study how to calculate the effects of applying an interest rate to monies both lent and invested using some Excel tools known as Financial Functions. To do so, let’s first look at how interest is calculated. Calculating interestSimple InterestAgain, interest is a fee that is paid for use of someone else’s money. A bank pays you interest on your savings account. You pay interest to your bank for the money they have lent you to buy a car. Interest that is paid solely on the original amount invested or lent is called simple interest. The computation of simple interest is based on the following formula:Simple interest = Principal * Interest rate per time period * Number of time periodsHere is an example using simple interest: You have invested $1000 in a savings account that pays 5% of the principal annually. At the end of each year you will take out the interest paid. How much interest will you have collected at the end of four years?Year 1 – Principal $1000 * Interest rate .05 = $ 50Year 2 – Principal $1000 * Interest rate .05 = $ 50Year 3 – Principal $1000 * Interest rate .05 = $ 50Year 4 – Principal $1000 * Interest rate .05 = $ 50Total 4 year Interest: = $200Another example would be a loan for $1000 with 5% annual interest due at the end of each year and the original principal amount ($1000) payable at the end of the four years. Here each year the borrower would owe $50, and then at the end of four years owe the original principal amount.First year:Interest $1,000 * 0.05 = $50Second year:Interest $1,000 * 0.05= $50Third year:Interest $1,000 * 0.05= $50Fourth year:Interest $1,000 * 0.05= $50Total Debt SUM() SUM() =$1000Notice that in both of these examples the principal, or the amount of the original investment or loan, never changes. Coupon bonds work in this way, where the interest is always removed after each period. However, most financial instruments such as savings accounts, zero-coupon bonds, certificates of deposit, mortgages, and car loans usually assume that the interest from previous periods is either added or subtracted to the principal amount each period. Compound INTEREST - SavingsNow consider the original example of investing $1000 at 5% annual interest over a period of four years, but this time the interest will be reinvested at the end of each period. In other words, the amount of earned interest will be added to the principal at the end of each period. How will this affect the total interest earned? When interest earned each period is added to the principal for purposes of computing interest for the next period, this is known as compound interest. As shown in the examples below, the total value of interest payments using compounding is greater than that of the interest payments using a simple interest of the same percentage. Most financial instruments use compounding; these include bank accounts, certificates of deposits (CD’s), loans, etc. To determine how much interest is earned over a 4-year period, break down the payments by the compounding period, in this case yearly. The principal in year 1 is $1000 which then earns $50 of interest. At the beginning of year 2 the principal is now $1000 plus $50 ($1050) and interest is now computed on this new amount, resulting in $52.50 in interest during year 2. This pattern continues in subsequent years. The total interest earned on this investment is $215.51. This is $15.51 more than if only simple interest is used.Year 1 – Principal $1000.00 * Interest rate .05 = $ 50.00Year 2 – Principal $1050.00 * Interest rate .05 = $ 52.50Year 3 – Principal $1102.50 * Interest rate .05 = $ 55.13Year 4 – Principal $1157.63 * Interest rate .05 = $ 57.88Total 4 year Interest: = $215.51So when calculating compound interest it is critical that the calculations are broken up into the periods of compounding using the corresponding interest rate per period. Otherwise, the correct values for interest paid will not be obtained.Another example is as follows: Assume that Ying has deposited $1,000 in a credit union, which pays interest at 8% per year compounded quarterly. Determine the amount of money Ying will have on deposit at the end of 1.5 years assuming all of the interest is left in the savings account. Quarter 1 – Principal $1000.00 * Interest rate .08/year ÷ 4 quarters/year = $ 20.00Quarter 2 – Principal $1020.00 * Interest rate .08/year ÷ 4 quarters/year = $ 20.40Quarter 3 – Principal $1040.40 * Interest rate .08/year ÷ 4 quarters/year = $ 20.81Quarter 4 – Principal $1061.21 * Interest rate .08/year ÷ 4 quarters/year = $ 21.22Quarter 5 - Principal $1082.43 * Interest rate .08/year ÷ 4 quarters/year = $ 21.65Quarter 6 - Principal $1104.08 * Interest rate .08/year ÷ 4 quarters/year = $ 22.08Total Interest: = $126.16Total savings: = $1126.16Note that if the annual interest is 8%, the quarterly interest is 8% divided by 4 quarters per year or 2%. Also notice that the compounding has been performed six times, corresponding to the number of quarters in 1.5 years (1.5 years * 4 quarter/year = six quarters). The total amount at end of 1.5 years is $1,000 + $126.16 = $1126.pound INTEREST - LoansLoans also work differently than the example given in the simple interest section. Normally a loan is made for an original face amount, the initial principal, at a given interest rate. If the loan is paid off in equal monthly installments, then each month the borrower will pay interest on the remaining principal plus a portion of that principal. For example, consider a car loan of $10,000 at 12% annual interest compounded monthly with a monthly payment of $888.49 payable over one year. This transaction is illustrated in Figure 1. Figure 12590165-635In the first month of the loan the accrued interest expense would be $10,000 times the monthly rate of interest. The monthly rate of interest is calculated as 12% divided by 12 months per year or 1% per month. This amount is $100. So of the $888.49 payment, $100 is used to pay the interest expense and $788.49 is applied toward lowering the remaining principal. The new principal at the beginning of period 2 is becomes $10,000-788.49 = $9211.51. In period 2 the interest expense is calculated by multiplying the new principal $9,211.51 by the 1% monthly rate of interest for an interest expense of $92.12. The amount $888.49 - 92.12 = $796.37 is applied toward reducing the principal. This repeated reduction of principal is illustrated in Figure 1 and is sometimes referred to as an amortization schedule. The loan would be paid off at the point where the remaining principal value is $0. Using Financial Functions to Calculate Compound InterestAs you can see, the calculation of compounding even for a few periods can become tedious. Imagine the calculation for a 30-year mortgage that is compounded monthly: there would be 12*30=360 calculations. Excel provides a set of built-in functions to perform these calculations. The user need not understand the detailed mathematics or repeat the principal/interest calculations for each period of an investment or loan. You need only to know which function to use and how to use it. The spreadsheet program takes over from there, performing the often complex calculation and returning the result. The variables 13335001356995As already seen in our compound interest examples, a financial transaction requires several component pieces of information to calculate interest. These include the original amount of the financial transaction (loan or deposit), an interest rate, the duration of the transaction in terms of the number of times it is compounded, and the ending value of the transaction. Each of these pieces of information is a term in a complex formula which can simulate the step-by-step compounding approach that was just presented:The good news is you never need write this formula or solve for the variable you need to determine. A set of five functions are available within Excel to do this: PV, RATE, NPER, PMT, FV. Select the function for the value to be calculated and then supply the other four terms as the function arguments. Understanding this complex mathematical formula is not required. What is required is the knowledge of what these terms are and how to apply them correctly. To explain the meaning of each of these terms, look at the diagram in Figure 2 representing a loan for the amount of $100 payable in equal quarterly installments over a period of two years. Beginning PV $100End FV $0PMTPMTPMTPMTPMTPMTPMTPMTFigure 2The Present Value of this loan, represented by the term PV, is $100. This is the amount of money (cash flow) into or out of a financial transaction at the beginning of the transaction.The Rate, represented by the term RATE, is interest rate per period. If the interest rate is 8% per year compounded quarterly, then the rate per period in this transaction will be the quarterly interest rate of 8%/4 or 2%.The Number of Periods, represented by the term NPER, is the duration of the loan. This is a two-year loan compounded quarterly, so the number of periods is 2 years * 4 quarters per year for a total of 8 quarters.The Payment, represented by the term PMT, is the amount that is paid in equal installments each period. This payment may include periodic interest and a portion of the principal. If there are 8 periods, then there will be 8 payments of this specified amount.The Future Value, represent by the term FV, is the final amount (cash flow) into or out of this transaction. In a loan, if the transaction is completely paid off this amount will be zero. If money is put into savings and compound interest accrued, this will be the value at the end of the transaction’s duration including the original principal, any periodic payments, and accrued interest.Each of these terms can be solved for using the corresponding Excel function where the other four terms being the arguments of that function. USING the FV function to find future valueLet's take another look at the compound interest example where Ying has deposited $1,000 in a credit union which pays interest at 8 percent per year compounded quarterly. Our goal is to determine the amount of money on deposit at the end of 1.5 years if all interest is left in the savings account.In this problem the value being sought is the final value of the transaction which is the future value (FV). The inputs are the remaining terms: the RATE is 8% per year compounded quarterly or 2% per quarter, the number of periods (NPER) is 1.5 years times 4 quarters per year or 6 quarters, and the original value (present value PV) of the transaction is $1000. Since there are no periodic payments (PMT), that value is $0/quarter.The function to calculate Future Value is as follows:= FV (rate, nper, pmt, pv, type)Substituting the values from this example into the function gives the formula =FV(.08/4,1.5*4, 0, -1000) resulting value is $1126.16.Notice two things which may not appear clear in this example:Why is the PV argument (present value) a negative value, -1000? What is the type argument and why is it missing?Cash flowTo understand why -$1000 was substituted into this formula rather that +$1000, it is necessary to understand how cash into and out of a financial transaction is represented. The FV, PV and PMT arguments are all cash amounts that are either received or paid out. These inputs and outputs are referred to as cash flow. In order for these financial functions to work properly, the computer must understand which amounts are flowing to you or from you. The algorithm used in these Excel financial functions requires that when cash is received it is considered positive cash flow, and when cash is paid out it is considered negative cash flow. In this problem Ying gives the bank the $1000 at the beginning of the transaction. Though the bank account belongs to Ying, the cash has flowed from Ying to the bank and thus is a negative cash flow. At the end when Ying retrieves her principal and interest the monies will flow back to her, resulting in a positive future value.The type argumentThe last parameter in the FV function is type. The type argument designates when payments are made. There are two different values the type argument can be: Type 0 = payments made at the end of the period (default)Type 1 = payments made at the beginning of the periodThere is a difference in the total amount earned if payments are added at the beginning of a period vs. at the end of a period. Most transactions are type 0, and if this is the case this argument can be left out. A type 1 financial instrument example is Treasury Bills. Here the investor sends the US government their money and the treasury immediately sends back the interest for the duration of the financial transaction (3 month, 6 month, etc). At the end of the Treasury Bill duration the principal amount is returned to the investor. If an investor is given their interest now versus 30-days from now, this money can be invested somewhere else and presumably earn addition interest. Thus earning 5% in a type 1 financial investment is worth more than earning 5% in a type 0 financial investment. Designating the correct type will account for this when calculations are performed.Using financial functions – some additional hintsThere are a few additional points you should be aware of when using financial functions:Zero values occurring at the end of the function list: When the last argument or arguments of a function are zero, they can be left out completely. Thus the formula =FV(.08/4,1.5*4, 0, -1000) is equivalent to =FV(.08/4,1.5*4, 0,-1000,0).Zero values that occur in between other values: An argument to a financial function that occurs before non-zero values, such as the pmt argument in this formula, must be explicitly written or at least a comma used to indicate to the computer that the next value read corresponds to the next argument of that function. Thus the formula =FV(.08/4,1.5*4,0,-1000) can be written as =FV(.08/4,1.5*4,,-1000). This formula cannot be written as =FV(.08/4,1.5*4,-8000). In the latter expression the computer will interpret the -1000 as the periodic payment, and assume $1000 is paid each quarter rather than just at the beginning of the transaction, resulting in a significantly higher Future mas may not be used as part of values: The amount -1000 may not be typed into the spreadsheet as -1,000. A comma used as part of a value will signal to the computer that the next argument is about to start. So in the formula =FV(.08/4,1.5*4,0,-1,000), the computer will assume that the Present Value is -1 and not -1000, and that the Future Value is zero.Correct argument order: All arguments must be entered in their correct order -- otherwise the function will not work properly.“Per Period”: The terms PMT, RATE, and NPER as used in these functions must be in terms of the compounding period of the transaction or the resulting calculation will be incorrect. For example, if a loan has a 12% per year interest rate and the loan is compounded quarterly, the rate per period (RATE) is 12%/4 = 3% per quarter. Likewise, if the loan’s duration is 5 years, the number of periods (NPER) is 5*4 = 20 quarters. The payment argument (PMT) must be the amount paid against the loan per quarter. Do not simply change PMT, RATE, and NPER to all correspond to years, they must correspond to the number of compounding periods for the function to work correctly. An FV Example – Using cell referencesFigure 3 is an example of using the FV function with a spreadsheet to analyze the ending balance of several different bank investments. The initial investment will be $5000. Data is provided for each alternative including the annual interest rate, the number of compounding periods per year (annually if 1, quarterly if 4, and monthly if 12), the additional payments made for the compounding period, and the total loan duration in years. Write a formula in cell F4 that can be copied down the column to determine the ending value of each of these investments.Figure 3Step 1: Calculating the value at the end of the financial transaction means calculating its future value.Step 2: A future value can be calculated using an FV function. The FV function has the following syntax: = FV (rate, nper, pmt, pv, type). The rate per year is given in column B and the number of periods per year is given in column C. The rate per compounding period can be expressed as the rate per year divided by the number of compounding periods per year: B4/C4.The number of compounding periods (nper) per year is given in column C. To obtain the total number of periods in the financial transaction, multiply the number of periods per year times the number of years. In this case we can express this as C4 * E4. Payments (pmt) are given by payment per period in Column D, so D4 can be directly substituted into the function. Is the cash flow positive or negative? Since money is being placed in the bank it should be considered a negative cash flow from the standpoint of the investor. Present value (pv) is defined as the value of the financial transaction at the beginning, as given in cell B1. Again, is this a positive or negative cash flow? Since the money is being put into the bank it should be considered a negative cash flow. No information is provided regarding payments so it can be assumed that payments will be made at the end of each period and the default type can be used.The resulting formula is = FV(B4/C4, C4 * E4, -D4, -B1)Step 3: Since this formula is being copied down the column, check to see which cell references, if any, are absolute. In this case only B1, the PV reference, is absolute. All other values vary relatively when copied. The final form of this formula should be as follows: = FV(B4/C4, C4 * E4, -D4, -B$1)Other Derived Formulas – PV, NPer, FV, RateIn the complicated formula originally presented at the beginning of this chapter, any single variable can be solved for by moving the terms around. Similarly, Excel allows you to solve for Present Value, Payment, Rate, and NPER by simply using their associated functions and providing the other needed variables. Here are the syntaxes for each of the related functions:Present Value: = PV (rate, nper, pmt, fv, type) PV computes the value at the beginning of a financial transaction based on a given interest rate, loan duration, periodic payment amount, and future value.Number of Periods: = NPER (rate, pmt, pv, fv, type) NPER computes the number of compounding periods based on a given interest rate, future value, present value, and periodic payment amount.Interest rate per Period : =RATE (nper, pmt, pv, fv, type) RATE computes the implied interest rate per compounding period that would give you the specified future value for the specified loan duration, periodic payments, and present value. Periodic Payment: =PMT (rate, nper, pv, fv, type) PMT computes the period payment amount that would be required for a given PV, FV, interest rate, and loan duration.Present Value Example:Karl has inherited some money and would like to put aside some of it for his daughter’s college education. His daughter is expected to need the money ten years from now and he would like to be able to give her $20,000 at that time. Write an Excel formula to determine the amount of money he should deposit now to meet this goal. Assume the money can be put into an educational IRA that is tax free and pays 10% per year compounded quarterly. Assume that no additional payments will be added to the principal.For a given Future Value (value at the end of ten years), the question asks to find the Present Value (value at the beginning of the financial transaction) given a specific duration and interest rate. This can be accomplished using the PV function.Translate to Excel syntax:The Rate is given as 10% per year. Since the compounding period is quarterly the rate per period is 0.1/4.To calculate the number of periods we take the loan duration of ten years and multiply by the number of periods per year, obtaining 10*4 periods.The problem specifies that there are no additional payments so PMT is 0.The FV is given as $20,000. Since this is going back to you it will be a positive cash flow.The formula will then be =PV(0.1/4, 10*4, 0, 20000).Step 3: In this example the formula is not copied. Thus absolute & relative referencing is not considered. NPER Example: William is saving for retirement and has rolled over the sum of $30,000 into his new employer’s 401K plan. This fund earns a guaranteed interest rate of 12% per year compounded semi-annually. Each period (1/2 year) he plans to deposit an additional $1000. He does not want to retire until he has at least $75,000 in this account. Write an Excel formula to determine how long (in years) this will take.The question requires the calculation of the loan duration in years. Since the financial transaction is compounded semi-annually, the number of semi-annual periods must be calculated first and then that number should be converted into years. Remember that calculated years directly in a financial function would imply yearly compounding, resulting in an incorrect value for a transaction compounded semi-annually. To convert the number of periods (NPER) to years, divide it by the number of periods per year -- in this case 2.Put the information into Excel syntax. Use the NPER function: =NPER(rate,pmt,pv,fv,type). The initial investment is $30,000 – this is the Present Value of the transaction. It is a negative amount since William will be putting the money into the 401K account.The interest rate is 12% per year and there are 2 periods per year. Thus, the rate per period is .12/2.There are periodic payments of $1000. The cash flow here is also negative.The value at the end of the financial transaction is given as $75,000 – this is the Future Value. Since we will be withdrawing these funds, they represent a positive cash flow.The resulting formula will be = NPER (0.12/2, -1000, -30000, 75000, 0)/2.This formula is not copied, so absolute and relative cell referencing need not be considered.Rate Example:The car dealer has offered Jonathan a deal on a car loan. He has told Jonathan that he can finance a new Honda and pay it off over the next five years with monthly payments of $290 and a 10% down payment. The sale price of the car is $17,500. Write an Excel formula to determine the annual interest rate Jonathan is being charged.The unknown here is annual interest rate. To obtain the annual interest rate, first calculate the interest rate per period using the RATE function. Then take the resulting periodic rate and multiply it by the number of periods per year to obtain the annual rate. Assume that a loan is compounded on the same schedule as its payment periods unless otherwise specified. Translate this into Excel syntax using the RATE function =RATE(nper,pmt,pv,fv,type):The initial value of the loan is $17,500 less 10% down (0.9*17500 or 17500-0.1*17500). Since the money is being lent to Jonathan so he can buy the car, it is a positive cash flow.The periodic payment he makes is $290 (negative cash flow)The duration of the loan is 5 years and we can assume that the loan is compounded monthly, giving us 5 * 12 or 60 periods.The FV of the loan is zero, assuming it has been completely paid off.There are 12 periods per years so the rate per year = 12 * rate per period.The resulting formula is = Rate(5*12,-290,17500*.9)*12.Again, this formula is not copied.Notice that the PV of the loan is 90% of the sale price of the car. A down payment made at the beginning of the financial transaction reduces the actual amount borrowed. Also, notice that there are only three arguments in this function. What about the FV and type of this transaction? If a loan is completely paid off, the FV is zero. Since we assume type 0, neither of these arguments need to be included. We could also write the formula like this: =RATE (5*2, -250, -17500*.9,0,0)*12PMT Example:Another car dealer has offered to sell Jonathan this same car for $17,500 with a 10% down payment. The dealer has offered him 4.9% financing (annual rate compounded monthly) to be paid back over the next four years in equal monthly installments. Write an Excel formula to determine the amount of the monthly car payment for this loan.Here the unknown is the periodic payment value. This can be calculated directly using a PMT function. In Excel, use the PMT function: =PMT(rate,nper,pv,fv,type)The Present Value is $17,500 less 10% down (17500*0.9).The interest rate is 4.9% compounded monthly so the interest per period is 4.9%/12.The duration of the loan is 4 years giving us 4 * 12 or 48 periods.The loan is fully paid off, so the Future Value is zero.The resulting formula is = PMT(.049/12,4*12,17500*.9)Again, this formula is not copied.Type 1 Example:15906751441450You have arranged for a student loan from a local bank. The bank will make quarterly disbursements of $1500 at the beginning of each quarter (all four quarters). No payments are made until the graduation, although interest is charged at the rate of 4?% per year compounded quarterly. You have prepared the following spreadsheet in Excel and need to write a formula in cell B8 to determine the final amount you will owe the bank upon graduation (assume the loan duration is four years). Figure 4 The key element to notice in this question is that disbursements (payments to you) are made at the beginning of each quarter. Thus a type 1 financial function will be required. Since the value at the end of the financial transaction is needed, an FV function will be required. The following are also known:Periodic payments will be made to you (positive cash flow) of $1500 per quarter.The rate per period is 4? %/4.The number of periods is 4 years * 4 periods per year. Since there is no initial disbursement of cash, the PV of the loan is $0.Putting this information into Excel syntax, write this formula in cell B8: =FV(B2/B6,B4*B6,B3,0,1)After graduation, you will pay this student loan back to the bank making equal monthly payments over a ten year period. The interest rate will continue to be 4 ?% but will be compounded at each payment period. Write an Excel formula to determine the value of these monthly payments. A periodic payment must be calculated. Using the PMT function, what are the values of each of the arguments?The rate per period is now 4 ?% divided by 12 periods per year (monthly).The number of periods is 10 years times 12 periods per year.The Present Value of the transaction is the amount you owe when you start making the payments. This is the value we calculated in cell B8.The Future Value of this transaction is $0 if we assume the loan is paid off at the end of ten years.The loan type, since not otherwise indicated, is 0. Payments are made at the end of each month. Substituting in the appropriate arguments, write this formula in cell B9:=PMT(B2/12, 12* B5, B8)What if the value in cell B8, the value of this loan at the end of the four years, had not been previous calculated? Can the payment still be calculated? Yes, one can nest these functions to solve this problem: =PMT(B2/12, 12* B5, FV(B2/B6,B4*B6,B3,0,1)). Here the Present Value argument of the PMT function is the nested Future Value function (the same one we wrote in the previous example). Since the value “borrowed” at the beginning of this 10 year loan is future value of the payments made to us in the preceding four years this is equivalent to the previous PMT formula. A Balloon PaymentEmma intends to buy a car and has applied for a $15,000 loan. The bank charges 8?% annual interest compounded monthly. The loan will be paid back over a 3 year period. At the end of the three year period an additional $500 will be due in order to completely pay off the loan. Write a formula to calculate the monthly payment amount.This question is similar to many of the ones already seen, except for the fact that an additional amount is due at the end of the loan. This final amount is known as a balloon payment. A balloon payment is a negative cash flow at the end of a transaction; it can be considered a negative Future Value. To include such a payment in our formula we would write: =PMT (.085/12, 3*12, 15000,-500)Will the new payment be higher or lower than for a similar loan that has no balloon payment? Think about what is happening in the financial transaction. If the entire amount of the loan is paid back (no balloon payment) more is paid back per period than if we pay down the loan amount to $500 and make a final payment of $500 to pay off the loan. Thus, one would expect that if a balloon payment is required on a loan, the periodic payments will be less than for a similar loan with no balloon payment.Exercise 1.10-1 Practice with Financial FunctionsPMT(rate,nper,pv,fv, type)FV(rate,nper,pmt,pv, type)PV(rate,nper,pmt,fv, type)RATE(nper,pmt, pv,fv, type)NPER(rate, pmt, pv, fv, type) 1. You are investing $5000 into a savings plan today and will make quarterly contributions of $100 per quarter. The plan pays 6% interest per year compounded quarterly. Write an Excel formula to determine how much your savings will be worth in 5 years. __________________________________________________________________2. Write an Excel formula to determine the yearly interest rate being charged by the bank on a $175,000, 30- year mortgage. You make a monthly mortgage payment of $2000 and the value of the loan at the end of thirty years is zero. Interest is compounded monthly. __________________________________________________________________3. Write an Excel formula to determine the value today of $1000 invested 2 years ago at 12% per year compounded quarterly.__________________________________________________________________4. Write an Excel formula to determine the monthly car payment that will be required to take a $10,000 loan over 4 years. The rate of loan is 15% per year compounded monthly__________________________________________________________________5. (a) Write an Excel formula to determine the amount of money needed to invest today at 6% per year compounded monthly to have $5000 in three years. Monthly payments of $25 will be put into the account each month.__________________________________________________________________(b) Rewrite the formula to determine how much would be need to be invested if no additional monthly payments will be made.__________________________________________________________________You are buying a Jeep for $23,500 with a $2000 down payment. The rest you are borrowing from the bank at 6.5% annual interest compounded monthly. Your monthly payments are $350. Write an Excel formula to determine how many years it will take to pay off the loan.__________________________________________________________________In Excel financial functions, cash out of pocket is expressed as a ___________________ (negative/positive) number.The last argument of each financial function is “type.” What does “type” mean? What are the different types?____________________________________________________________________________________________________________________________________________________________________________________9. For the next 3 years you will be receiving $2000 per quarter at the beginning of each quarter from the state of Ohio as an educational loan. The loan rate is 6% compounded quarterly. Write an Excel formula to determine the Present Value of this loan? (hint: consider the “type”)__________________________________________________________________10. I found a cookie jar with a bank note in it from 1900. The value in 1900 was $100 and the bank, which still exists, promises to pay 3% per year compounded annually. Write an Excel formula to determine how much the bank note is worth now.__________________________________________________________________ 11. I decided to take a mortgage with a balloon payment. It is a $100,000 mortgage at 6% annual interest compounded monthly for 20 years. In 20 years I will make a balloon payment of $10,000. Write an Excel formula to determine my monthly mortgage payment.__________________________________________________________________ 12. Your parents have agreed to give you $500/mo for school for the first four years. Write an Excel formula to determine the present value of these payments assuming an annual interest rate of 2.5% annual interest compounded monthly?__________________________________________________________________ 13. You have a student loan for $5000 at 4.5% interest. No payment is required for 2 years but the loan accrues interest monthly. Once you begin paying the loan you have 10 years to finish payments. Write an Excel formula to calculate the monthly payment. (hint: try nesting your financial functions)__________________________________________________________________Exercise 1.10-2 Loan Analysis Worksheet28003535560You are about to buy your first home and have just met with several banks to discuss financing. At the end of a very long day, you’re totally confused. You’ve decided to create a spreadsheet to help you analyze the problem. You have listed the purchase price of the home and the different values for each of the loan variables.The Down Payment is the amount of money you will pay at the time you purchase your home. The difference between the sale price and your down payment is the face value of your mortgage loan.Points are additional charges banks sometimes require you to pay when you take out a mortgage. Banks usually offer mortgage loans in a variety of interest rate and point combinations. Frequently, you will find that loans with higher points have lower interest rates. One point equals one percent of the loan value; so 1 point on a $7500 loan is $75.Fees are more additional amounts the banks charge when taking out a mortgage. These amounts vary by bank and loan type. Typical types of charges are application fees, appraisal fees, credit report fees, etc. When answering the questions below, please use cell references wherever possible in your answers. Only part of the spreadsheet you have created is shown above. For some questions, you will be writing formulas for cells not shown. Write an Excel formula in cell G6 that can be copied down the column to calculate the face value of the mortgage: the purchase price of your new home less the down payment.Write an Excel formula in cell H6 that can be copied down the column to calculate the mortgage payment for this loan amount (G6) over the duration of the loan at the nominal interest rate indicated. Assume the loan is compounded monthly. Write an Excel formula in cell I6 that can be copied down the column to calculate the actual amount you will borrow after the points and fees have been deducted.To take these fees into account, your lender is required by law to tell you the APR of your loan - actual percentage rate of interest being charged. The APR calculation should be based on the payments you calculated in column H and actual amount borrowed that you calculated in column I, and the duration of the loan (compounded monthly). Write an Excel formula in cell J6 that can be copied down the column to calculate the rate (APR) actually being charged for this mortgage. The loan in Option 1 and the loan in Option 4 require the same down payment and are for the same duration. The interest rates and points vary. Which mortgage would you be better off with if you plan on owning your home for the next 30 years and which mortgage would you be better off with if you plan on only owning this home for the next 2 years? (Explain)Using the nominal interest rate and face value of the loan, determine the new monthly loan payment if you alter the loan to include a $10,000 balloon payment. Write your Excel formula in cell K6 and assume it will be copied down the column.The seller has offered you a private loan for 80% of the value of the house. They want you to pay $8000 per quarter for the next 10 years. Write an Excel formula, in cell L15 (not shown) to determine the annual interest rate they are charging. You are negotiating with the seller and tell him you are willing to pay $5000 per quarter at 7.5 % interest per year compounded quarterly. You will borrow everything but a 5% down payment. Write an Excel formula in cell L16 (not shown) to determine how many years will it take to pay off the loan.Eight years ago for your college graduation present your Mom gave you a bank CD worth $10,000. The CD earns 7.25% annual interest compounded yearly. Write an Excel formula in cell L17 (not shown) to determine (True/False) if you have sufficient funds from this CD for Option 1’s down payment.Exercise 1.10-3 Chapter Review – Financial Functions123825074930Use cell references wherever possible in your answers.(2 points) Write an Excel formula for cell F2 that can be copied down the column to determine the monthly payment of this loan (the car is purchased for $19,500, a down payment is made now of 10%, and the rest is financed at 6.9% annual interest compounded monthly). (1 point) The GM dealer is willing to negotiate on the loan duration. I told him I could afford $450 per month with no money down. Write an Excel formula to calculate how many years it would take me to pay off this loan at 1.9% annual interest compounded monthly.(1 point) The Chrysler dealer told me he would sell me the car for $400 per month with a 5-year payback and no down payment. Write an Excel formula to determine the annual interest rate I would be paying assuming the loan is compounded monthly?(2 points) A distant aunt has just left me a bank CD that she purchased ten years ago for $5000. The CD has been accruing interest at the rate of 8% per year compounded quarterly. Write an Excel formula to determine if I have enough money to buy the Honda for cash (true/false). (1 point) My friend said he put money away five years ago in a guaranteed return fund paying 6% annual interest compounded monthly. Each month since then he has deposited another $200. Now he has $18000 available to buy a car. Write an Excel formula to determine how much he put into the fund 5 years ago. (2 points) Another option the Ford dealer has offered me is to sell the car to me with financing for 3 years at 3.9% annual rate compounded monthly with no down payment, but with a $2000 balloon payment at the end of the loan. Write an Excel formula to calculate the monthly payment of this loan. (1 point) You decided to put off buying the car and have put the $9500 into a zero coupon bond that accrues 5% interest each year at the beginning of each year. These bonds make no payments until they mature in two years. What is the Future Value of this bond after two years? ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download