VIEWS: 37 PAGES: 2 CATEGORY: Childrens Literature POSTED ON: 11/28/2009
CH10pr04---Blinn-College
BUSINESS MATH USING EXCEL Name ______________________________ Date ______________________________ A 1 Chapter 10 - Practice 04 2 B 3 A. Calculate the amount financed, the finance charge, and the monthly payment. First State Bank financed Penny’s 3-year loan for $15,000 so she could buy new computers 4 for her real estate office. The bank charged 6.5% interest and required 10% down. 5 Purchase Price 6 Interest Rate 7 Down Payment Percent 8 Years for Loan 9 Amount Financed 10 Finance Charge 11 Monthly Payment 12 13 Directions: $15,000.00 6.5% 10% 3 14 15 16 17 18 19 20 1. In Cell B9, enter a formula to calculate the Amount Financed. (Multiply the Purchase Price by the complement of the Down Payment Percent.) (The Excel formula is =B5*(1-B7).) 2. In Cell B10, enter the formula to calculate the Finance Charge. (Use the formula I = P x R x T .) (The Excel formula is =B9*B6*B*.) 3. In Cell B11, enter the formula to calculate the Monthly Payment. (Divide the amount to be repaid by the number of monthly payments.) (The Excel formula is =(B9+B10)/(B8*12).) 4. Format dollar amounts for Currency, 2 Decimal places, and $. 5. Leave this screen open to use in part B. 21 B. Calculate the interest rebate, the balance remaining, and the loan payoff. Penny decided to pay off the loan after 30 months. By paying off the loan early, she is entitled 22 to a rebate on the Finance Charge. 23 Number of Payments 24 Payments Remaining 25 Sum of Digits Payments Remaining 26 Sum of Digits Total Payments 27 Interest Rebate 28 Balance Remaining 29 Loan Payoff 30 31 Directions: 36 6 32 33 34 35 36 37 38 39 1. In Cell B25, enter a formula to calculate the Sum of Digits Payments Remaining. (Use the formula N (N +1) divided by 2, where N = the Number of Payments.) (The Excel formula is =B24*(B24+1)/2.) 2. In Cell B26, enter a formula to calculate the Sum of Digits Total Payments. (The Excel formula is =B23*(B23+1)/2.) 3. In Cell B27, enter a fomula to calculate the Interest Rebate. (Multiply the Finance Charge by the rebate fraction.) (The Excel formula is =B10*(B25/B26).) 4. In Cell B28, enter a formula to compute the Balance Remaining. (Multiply the Monthly Payment by the Payments Remaining.) (The Excel formula is =B11*B24).) 5. In Cell B29, enter a formula to find the Loan Payoff. (Subtract the Interest Rebate from the Balance Remaining. (The Excel formula is =B28-B27.) 6. Format dollar amounts for Currency, 2 Decimal places, and $. 7. Save the file as ch10pr04a.xls. BUSINESS MATH USING EXCEL Name ______________________________ Date ______________________________ A 1 Chapter 10 - Practice 04 (Answers) 2 B 3 A. Calculate the amount financed, the finance charge, and the monthly payment. First State Bank financed Penny’s 3-year loan for $15,000 so she could buy new computers 4 for her real estate office. The bank charged 6.5% interest and required 10% down. 5 Purchase Price 6 Interest Rate 7 Down Payment Percent 8 Years for Loan 9 Amount Financed 10 Finance Charge 11 Monthly Payment 12 13 Directions: $15,000.00 6.5% 10% 3 $13,500.00 $2,632.50 $448.13 14 15 16 17 18 19 20 1. In Cell B9, enter a formula to calculate the Amount Financed. (Multiply the Purchase Price by the complement of the Down Payment Percent.) (The Excel formula is =B5*(1-B7).) 2. In Cell B10, enter the formula to calculate the Finance Charge. (Use the formula I = P x R x T .) (The Excel formula is =B9*B6*B8.) 3. In Cell B11, enter the formula to calculate the Monthly Payment. (Divide the amount to be repaid by the number of monthly payments.) (The Excel formula is =(B9+B10)/(B8*12).) 4. Format dollar amounts for Currency, 2 Decimal places, and $. 5. Leave this screen open to use in part B. 21 B. Calculate the interest rebate, the balance remaining, and the loan payoff. Penny decided to pay off the loan after 30 months. By paying off the loan early, she is entitled 22 to a rebate on the Finance Charge. 23 Number of Payments 24 Payments Remaining 25 Sum of Digits Payments Remaining 26 Sum of Digits Total Payments 27 Interest Rebate 28 Balance Remaining 29 Loan Payoff 30 31 Directions: 36 6 21 666 $83.01 $2,688.75 $2,605.74 32 33 34 35 36 37 38 39 1. In Cell B25, enter a formula to calculate the Sum of Digits Payments Remaining. (Use the formula N (N +1) divided by 2, where N = the Number of Payments.) (The Excel formula is =B24*(B24+1)/2.) 2. In Cell B26, enter a formula to calculate the Sum of Digits Total Payments. (The Excel formula is =B23*(B23+1)/2.) 3. In Cell B27, enter a fomula to calculate the Interest Rebate. (Multiply the Finance Charge by the rebate fraction.) (The Excel formula is =B10*(B25/B26).) 4. In Cell B28, enter a formula to compute the Balance Remaining. (Multiply the Monthly Payment by the Payments Remaining.) (The Excel formula is =B11*B24).) 5. In Cell B29, enter a formula to find the Loan Payoff. (Subtract the Interest Rebate from the Balance Remaining. (The Excel formula is =B28-B27.) 6. Format dollar amounts for Currency, 2 Decimal places, and $. 7. Save the file as ch10pr04a.xls.