payment due at termine the payment that must be are investin

payment due at termine the payment that must be are investing S3,000 in a that o 300 per quarter. The plan pays an today and will make additional ning of each period. Write an Excel formula to determine how will be worth in five years. 3% interest per year compounded vings nciaExce ormula to determine the amou nt of money that can be depreciated packaging machine purchased by a usinhe machine originally cost S150,000 and has a useful life of 5 years straight lne deprec iation, for a new vage value of $5,000 ating Loan Options for Flowers By Diana Diana has decided to purchase her florist Ae mds to analyze her options. She lists the purchase price of the building nts space for her small florist business. As her business continues to to purchase her own building. She has selected a site and now req ntly re $ ng withseveral banks to discuss financing options for a mortgage Diana Fina dra she es for each of the loan variables together with the other data inputs in an rcok named Loan.xlsx. Her analysis must also take into account the following: boo yment-The amount of money Diana will pay at the time she purchases the on Paovided is the percent of the building purchase price that will be required ment on cach corresponding loan. The difference between the sale price o the down payment is the loan for a down value-the face value of the loan. usually offer mo Frequently, lo The additional charges banks sometimes require when lending a mortgage. pointstally offer morgage loans in a variety of interest rate and point combinations. ns with higher points have lower interest rates. One point equals 1%? an value, so one point on a $7,500 loan is $75. itional amounts banks sometimes charge when lending a mortgage amounts vary by bank and loan type. Typical charges include application fees, noraisal fees, credit report fees, and so on. r task is to complete the Loan worksheet for Diana, using cell references whenever soble. Write the formulas in cells G8 through K8 so that they can be copied down the

Solution

1. As given in the question, please save the file name from Loan.xlsx to Loan Analysis.xlsx. ?You can right click on the worksheet and select Rename option.

2. As given, Purchase Price of the mortgage is $500,000 and the same is given in the cell E3. You can go to cell E3 by using cell number box in the formula bar. Understanding cells is easy, Columns run albhabatically like A, B C etc. and Rows run numerically like 1,2,3 etc. Therefore, for going to cell E3 refer the cell where Column E and Row 3 intersects and then you will find value purchase price $500,000.

Now, refer cell G8 to G14 wherein we need to calculate the Loan Value. As given, Loan value equals to Purchase price of mortgage minus Down Payment (% given in cell B8 to B14). First go to cell G8, In this question, put = and then select E3 cell then put - (B8*E3), then enter. It will return the Loan Value. Now freeze the E3 cell in the formula by clicking between E3 and put F4 function key for both the E3 inputs. It will make E3 as $E$3. Now copy that cell (right click and copy) and paste (right click and paste) in the below cells G9 to G14. Now, we have all the loan values.

3. Next step is to calculate the \"Mortgage Payments\". It is given in the question that Loan options 1-7 are all compounded monthly but in the excel worksheet, it is given that this line should be read as Loan option 1.-7 are all compounded quarterly not monthly. Therefore, we are going to calculate quarterly mortgage payments, please note that compounding periods / year given are 4 meaning that compunding is to be done quarterly only. Another assumption for point no. 3 as given in the question is that there is no ballon payment and the loan will be fully paid off in the given period in column D.

First, we will change the interest rate to quarterly compounding by starting with putting \"equals\" sign in cell C8 and then dividing it by 4. It will give us quarterly compuding interest rate and this excercise has to be done for all the cells from c8 to c14. Make sure to conver the percentage in number format. Next step is to convert the duration in to the number of quarters, for which we will multiply the number of years given in cell D8 - D14 with 4 (number of quarters).

Next step, is to calculate the mortgage payments quarterly, for this put this formula in cell H8

= =G8((C8(1+C8)^D8)/((1+C8)^D8)-1))

You will get the values of quarterly payment.

4. You have to deduct the Fees and Points value from the loan amount and you will get the actual amount borrowed. Fees and Points are deducted by the bank and they are not the value which borrower got in hand. For points, 3 points meand 3% of the loan value. Therefore, for loan value $400000, points value will be = $400000*3% = $12000 and Fee is given in absolute amount. So for Cell I8 Actual amount borrower will be = $387,625. Likewise, complete it for all the cell I9 to I14.

I hope it will give you more clarity and you can post your other questions as a new post. Thanks.