Excel Finance Functions

The first question involves some comparisons of a 15-year and 30-year mortgage with Excel functions PMT, CUMIPMT, CUMPRINC and FV.

Question One:  A person is considering taking out a $180,000 mortgage and must choose between a 15-year FRM and a 30-year FRM.   The interest rate on the 15-year mortgage is 2.90 while the interest rate on the 30-year mortgage is 3.40.   

What are the monthly payments on the two loans?

What are the total interest payments on the two loans for the first five years of the loan?

What are total principal reductions for the two loans over five years?

What is the loan balance after five years

Answer to Question One: 

The second question involves calculating interest payments on loans for two consumers using the IPMPT function.

Question Two:  Consider two people one with good credit and one with bad credit.   Each person has a car loan, a 30-year FRM, and a personal student loan.   The date of origination of each loan, the term of each loan, and the interest rate on each loan for the two people are presented in the table below.  What was the interest paid on these loans for the two people in 2014?

Loan information for Two People
Bad Credit Rate
Good Credit Rate
Term of Loan in Years
Loan Origination Date
Initial Loan Balance
Car Loan
Personal Student Loan   Rate
Mortgage Rate

Answer to Question Two:   

The third question illustrates the use of the EFFECT function to obtain the effective interest rate given an APR and number of payments per year.

Question Three:   Consider three loans with annual percentage rates of 4.0%, 8.0% and 12.0% respectively.  What is the effective interest rate on each loan if mortgage payments are received monthly?   What is the effective interest rates on the loans if payments are weekly?

How does the relationship between annual percentage rates and effective rates vary with the level of interest rates?   How is the relationship between APR and effective rates impacted by the number of payments in a year?

Answer to Question Three:

This question on the yield of a mortgage with points uses the PMT function, the IRR function and the EFFECT function:

Question Four:   Consider a 30-year Fixed Rate Mortgage with a 6.0 % APR.   What is the yield on the loan if the lender charges 0 points, 1 point, or 2 points?   Assume the loan is held for all thirty years.

Answer to question on yield of mortgage with points:

Post uses PMT, CUMIPMT, IRR and EFFECT to calculate a mortgage yield when there are points and the mortgage is paid off early.

Prepayment and Mortgage Yields

Question Five:   What is the yield on a 30-year FRM mortgage with a loan balance of $500,000 where 2 points are charged if the mortgage is paid off after five years?

Question six applies the XIRR function to the problem of returns from an ETF for different investment and disbursement periods.   (Should the statistics presented here replace current returns statistics for funds?)

Question Six:   A person invests $1,000 per month in investment fund VFIAX starting on 1/2/02 for sixty months.   After the sixty month period the person sells (1/36) of the shares in the account at the date of the last deposit.  What is the internal rate of return from this investment.

Go here for a complete answer to the second question:

Bonus Problem:   A different person invests starts the same process of sixty monthly contributions in VFIAX starting in 1/02/2003  followed by 36 monthly disbursements where 1/36 of shares at the date of the last deposit are sold each month.  Collect data and set up the spread sheet to calculate the internal rate of return for this second investor.

What are returns for the second investor?   Comment on risk of this investment over these holding periods?

A discuss of results for the two investors can be found here.

Problem illustrates the use of XIRR and XNPV.   I calculate net present value of irregularly timed cash flow with and without XIRR and happily get the same answer.

Question Seven:  Below are a list of purchases and disbursements of shares of VOE Vanguard Value Mid-Cap fund along with the date of each transaction.   What is the internal rate of return from the investment in VOE?

What is the net present value of these cash flows when the cost of capital is 10 percent?   Solve this problem using XNPV and by creating a spreadsheet with the discount factors for each cash flow.   Show the two methods provide identical results.

Purchases and Disbursement of VOE

Answer available here;

Question Eight: A person graduates from college and graduate school with $100,000 in student debt.   The interest rate on a 10-year student loan is 5% per year.   The person wants to buy a house that costs $300,000 with a 90% LTV loan. The home mortgage interest rate is 4.5% on a 30 year FRM.

Assume that in order to qualify for the house the person must meet two conditions.   

Constraint One:  The ratio of mortgage interest to income must be less than 0.28.  

Constraint Two: The ratio of total interest (mortgage and non-mortgage) interest must be less than 0.38.

How much income does this person need to qualify for a loan on this house? 

Why might student debt have a smaller impact on the purchase of a $700,000 home than the purchase of a $300,000 home.

Answer to Question Eight Here:

Question Nine:
Assume the person described in question eight also has $10,000 in credit debt at 12 percent and a car loan of $20,000 at 6 percent in addition to the $100,000 student loan.   What monthly income would qualify this person for the purchase of a $300,000 home at 90 percent LTV?   What monthly income would qualify for the person to purchase the $300,000 home at 80% LTV.

Create and document a spread sheet that will give monthly income needed to qualify for a mortgage for a wide variety of home prices, LTV ratios and different holdings of student and consumer debt.

No answer has been created for this problem yet.

Question Ten:   A person is buying a $300,000 house.   She is taking out a 30-year FRM loan with an interest rate of 4.0 percent.   She wants the loan balance to be $170,000 in ten years.   What down payment should she place on her house to assure that the loan balance is $170,000 in ten years?

Go Here for the Answer to Problem Nine:

Question Eleven:   An investor provides a homebuyer with $40,000.   The homebuyer uses the funds as part of a down payment on a $200,000 home. The house appreciates at the rate of 5 percent per year.

In exchange for the $40,000 the homeowner agrees to return the $40,000 investment plus 40 percent of the capital gain to the investor.  

Create a spreadsheet that calculates the IRR of this investment for different holding periods.   Present results for holding periods of 3, 7, 10, 15 and 20 years.

Answer to Shared Appreciation Mortgage Problem Found Here:

Question Twelve:    A person buys a bond on June 1, 2018.    Under the terms of the bond, the owner receives two payments – one of $5,000 on 6-1-24.   The other of $7,000 on 6-1-26.   The market yield on the bond on the day it is purchased is 12 %.

How much did the buyer pay for the bond?

Verify that your answer is correct with the XIRR function in Excel?

Answer tot he Discounted Cash Flow of Bond Payment Problem found Here:

Question Thirteen:   Consider a person with $15,000 in credit card debt at a 12 percent interest rate.   A person is considering two repayment strategies on the credit card.  

Strategy one involves pay $450 per month 3 percent of $15,000 or $450 until the card is repaid.

Strategy two involves paying 3 percent of the initial monthly balance each month where the initial monthly balance in the current month is equal to the previous month initial balance minus the principal reduction from the payment in the previous month. 

Assume that payments are made at the end of period.

Describe the difference in the trajectory of the loan balance for these two repayment strategies.   

Go here for the question and answer of the credit card replacement strategy problem.

No comments:

Post a Comment