Question: In the example, the first value for the Principal PMT was 199.10 in cell E18. Answers for the questions must follow the example answer.

In the example, the first value for the Principal PMT was 199.10 in cell E18. Answers for the questions must follow the example answer.

(1-2 use first picture)

1. Calculate the Principal PMT and Balloon Payment using IF statements. with respect to cell C12

2. Calculate the Principal PMT and Balloon Payment using True/False Statements (Boolean Expressions (T/F))

(3-5 use second picture)

3. Calculate the Principal PMT and Balloon Payment using Vlookup Function with respect to cell C6

4. Calculate the Principal PMT and Balloon Payment using IndexMatch with respect to cell C6

5. Calculate the Principal PMT and Balloon Payment using OffsetMatch function with respect to cell C6

4 5 6 7 8 9 10 11 12 13 14 15

4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 no 4 6 7 B Inputs Original Balance Maturity Frequency Contract Rate Points Prepayment Penalty Moving Date Month 0 1 Inputs 2 Moving Date 3 3 4 5 Month 4 5 6 7 8 9 10 41 Month 0 1 2 6 7 8 9 10 Model 0 1 96 97 P 200,000 30 12 6.0% 2 5.0% 8 Model Balance 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000,00 200 000 00 VLOOKUP Table Payment Balance 8 1.00 1.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 Comments: Please use only IF() statements in columns E & F to make your model dynamic with respect to cell C12. Interest PMT 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000,00 1,000.00 4.000.00 Balloon Payment Interest PMT 1.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 Principal PMT Balloon Payment Principal PMT Monthly Payment Lender's Yield Prepayment. Penalty Balloon Payment. Monthly Payment Lender's Yield Outputs Outputs $1,199.10 4.56% Lender Cash Flows Prepayment Penalty (196,000) 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 Please use only VLOOKUP() in columns E & F to make your model dynamic with respect to cell C6. 1,000.00 1,000.00 1,000.00 Hint 1: VLOOKUP the month number into the VLOOKUP Table and multiply by the monthly payment minus the interest payment in column E and by the Balloon Payment in Column F. Hint 2: The Balloon Payment is equal to the balance at the beginning of the month minus the principal paid that month. $1,199.10 4.56% Lender Cash Flows (196,000) 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1.000.00

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

Loan Amortization Schedule with Flexible Calculation Methods This document outlines various approaches to calculate the principal payment and balloon payment within a loan amortization schedule The pr... View full answer

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Accounting Questions!

Take a Sample of 45 from the above Population using any of the probability-samplingmethods studied by you. State and describe clearly the method ofsampling used. the miss said not to use systematic...

Department Bonus If the Dept is 6007, enter 900, if it is 4305, enter 600, if it is 4312, enter 350, otherwise enter 200. Copy the function down for the employees. Calculate the total Department...

For each row in all 4 columns, add the number of heads (0-4 heads) for each row, and plot that on a histogram. Contrast the actual values with the expected values based on a binomial distribution for...

How to write the mathematical model for both the objective function and the constrains for this problem in written format. what would be the decision variables? (ex. X1,x2,x3) What insights we should...

D Should be gren for each year each of Que answers wn be worth 12 presum Unction appropriately for 14 is another point. Total of 10 points for Part A. Note that some years have been provided. Note:...

PowerPoint Assignment for BUS 401: Principles of Finance You have been asked by a manager in your organization to put together a training program explaining Net Present Value (NPV) and Future Value...

Tiffany owns an event management business that is currently operating from her garage. She would like to transform her guest room into a home office. However, she does not have her own capital to...

The attached file contains explanations on how to complete the excel assignment. You MUST follow all rules and formulas to get the solutions...etc/ Please send me the completed excel sheet that...

Due to blockage of fluid flow, the convection heat transfer coefficient for the finned and prime surfaces of a fin array in general decreases as the number of fins increases. Consider the fin array...

Problem 5: Find the velocity with which a particle must be projected along the interior of a smooth vertical hoop of radius a from the lowest point in order that it may leave the hoop at an angular...

Compute the integral: ( 6 x + 3 x 2 + 2 5 ) d x

The market price for Microsoft Corporation Closed at $19.40 and $30.48 on December 31, 2008, and 2009, respectively. The dividends per share were $0.46 for 2008 and $0.52 for 2009. a. Determine the...

Kathy Myers frequently purchases stocks and bonds, but she is uncertain how to determine the rate of return that she is earning. For example, three years ago she paid $13,000 for 200 shares of Malti...

Strategy, balanced scorecard, Service Company Snyder Corporation is a small information systems consulting firm that specializes in helping companies implement sales-management software. The market...

The Scottie Sweater Company produces sweaters under the Scottie label. The company buys raw wool and processes it into wool yarn from which the sweaters are woven. One spindle of wool yam is required...

Life insurance companies require applicants to submit to a physical examination as proof of insurability prior to issuing standard life insurance policies. In contrast, credit card companies offer...

Find the area enclosed by one leaf of the rose r=9cos(3).

How can a company improve its equity ratio?

What is the definition of accounting?

Step 1: Start up Integrated Accounting 8e. Step 2: Load opening balances file IA8 Problem 03-A. Step 3: Enter your name in the Your Name text box and click OK. Step 4: Save the file with a file name...

Consider the market represented in Figure 5P-5. a. Draw the consumer surplus and producer surplus if the market is functioning at the equilibrium price and quantity. Compute the total surplus if the...

Consider the market represented in Figure 5P-4. a. Calculate total surplus when supply is S1. b. Calculate total surplus when supply increases to S2.

Answer the following questions based on Tables 5P-1 and 5P-2. a. What is the quantity demanded at $10? What is the quantity supplied at $10? b. What is the quantity demanded at $25? What is the...