The first question involves some comparisons of a 15year and 30year 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 15year FRM and a
30year FRM. The
interest rate on the 15year mortgage is 2.90 while the interest rate on the
30year 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?
Answer to Question One:
http://www.dailymathproblem.com/2017/08/excelfunctionspmtcumipmtcumprinc.html
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 30year 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

0.11

0.04

6

1/1/13

$15,000

Personal Student Loan Rate

0.12

0.06

20

1/1/10

$45,000

Mortgage Rate

0.055

0.0325

30

6/1/13

$320,000

Answer to Question Two:
http://www.dailymathproblem.com/2017/08/theipmtfunction.html
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:
http://www.dailymathproblem.com/2017/08/theeffectfunction.html
This question on the yield of a mortgage with points uses the PMT function, the IRR function and the EFFECT function:
Post uses PMT, CUMIPMT, IRR and EFFECT to calculate a mortgage yield when there are points and the mortgage is paid off early.
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?)
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.
Answer to Question Three:
http://www.dailymathproblem.com/2017/08/theeffectfunction.html
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 30year 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:
http://www.dailymathproblem.com/p/excelfinancefunctions.html
Answer to question on yield of mortgage with points:
http://www.dailymathproblem.com/p/excelfinancefunctions.html
Prepayment and Mortgage Yields
Question Five: What is the yield on a 30year 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: 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.
Question Seven: Below are a list of purchases and disbursements
of shares of VOE Vanguard Value MidCap 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


Purchases


Date

Shares

Price

Amount

10/1/06

2000

43.9

87800

4/1/09

3000

28.1

84300

8/1/10

1700

38.5

65450

Disbursements


Date

Shares

Price

Amount

6/1/07

300

49.6

14880

10/1/13

2800

72

201600

5/1/16

3600

87

313200

Answer available here;
http://www.dailymathproblem.com/2017/08/useofxirrandxnpv.html
Question Eight: A person graduates from college and graduate school with $100,000 in student debt. The interest rate on a 10year 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 nonmortgage) 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.
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:
http://www.dailymathproblem.com/2014/10/studentdebtandqualifyingfor.html
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 30year 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:
http://www.dailymathproblem.com/2017/09/workingbackwardswithfvfunctionin.html
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:
http://www.dailymathproblem.com/2017/09/returnsonsharedappreciation.html
Answer to Shared Appreciation Mortgage Problem Found Here:
http://www.dailymathproblem.com/2017/09/returnsonsharedappreciation.html
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 6124. The other of $7,000 on 6126. 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.
Question Fourteen: A married couple filing jointly is taking
out a mortgage on January 1, 2018. The standard
deduction for the married couple is $24,000.
The mortgage interest rate is 4.0 percent. The couple has nonmortgage interest
deductions totaling $7,000. The loan
to value ratio for the house purchase is 90 percent.
What is the value of the home
where this married couple is indifferent between taking the standard deduction
and taking itemized deductions in 2018?
Question Fifteen: The chart below provides tax information for
a married couple under the new and old tax code.
Why is there no tax advantage
for purchasing a home under the new tax code?
Assume the old tax code was
in place for five years and total itemized deduction for our married couple
remained at $25,000. What is the net present
value of the tax advantage of purchasing a home and itemizing under the old
code for a fiveyear period?
(Assume the house is
purchased on January 1, 2018 and that the tax benefits are realized on, April
15 starting in 2019. Also assume the
cost of capital is 5 percent.)
Briefly discuss the potential
economic implications of the change on the tax code on the buy versus rent
decision, retirement savings, and migration of workers to new jobs.
Question Sixteen: A person makes several stock purchases and
places a stoploss order at 96 percent of the purchase price and a sell a limit
order at 104 percent of the purchase price.
Create a table that
calculates the average annual return on the transaction for various holding
periods under the assumption that a stock price either increases or decreases 4
percent?
What are the limitations of
using average annual rate of return statistics to measure daytrading returns?
What do these statistics
suggest about the risk of day trading?
Answer to question sixteen the day trading problem is here:
http://www.dailymathproblem.com/2018/02/annualrateofreturnandholding.html
Question Seventeen: Currently, lowincome undergraduate students
can take out a total of $31,000 in federal student loan. Subsidized student loans are only available
to people in lowincome households. The main
difference between subsidized and unsubsidized student debt is that the
government pays all interest costs on subsidized debt when the student is in
school while interest accrues on unsubsidized loans. The limit on subsidized student loans is
$23,000. The total limit on
undergraduate federal student loans is $31,000.
The Trump Administration is
proposing to eliminate all subsidized student loans. Create an Excel finance model, which can be used to estimate additional costs to student borrowers from the elimination of subsidized loan. How are these costs impacted by the number of years it takes for students to graduate?
Question Eighteen: A person has $200,000 in her 401(k) plan and 15 years left to work. She continues to contribute $500 per month to her plan. Scenario one involves 7 percent returns for 90 months followed by 4.0 percent returns for 90 months. Scenario two involves 4.0 percent returns for 90 months followed by 7.0 percent returns for 90 months. What is the difference between the 401(k) balance at retirement under the two scenarios? Discuss why and how uncertainty about path of market returns contributes to risk for 401(k) investors?
Answer to question eighteen:
http://www.dailymathproblem.com/2018/05/thepathofreturnsandfinancialrisk.html
Question: A person started periodic
investments in Vanguard growth fund (VUG) on 1/1/2015. One thousand dollars is
invested every six months.
The stock
price on the investment date and on 6/1/2018 are presented in a table below.
What is the
value of the fund on each date?
What is the
IRR of all investments in the fund on each date?
Why is
difficult to track the IRR of sporadic and periodic investments using IRR or
XIRR in Excel? How might these functions
be modified to be more useful and user friendly?
Stock Price of Vanguard Growth Fund


Date

VUG

1/1/15

98.6

7/1/15

106.6

1/1/16

97.2

7/1/16

109.8

1/1/17

113.7

7/1/17

129.1

1/1/18

149.9

6/1/18

153.7

Answer to question nineteen found here:
http://www.dailymathproblem.com/2018/06/trackingirrofperiodicinvestments.html
http://www.dailymathproblem.com/2018/06/trackingirrofperiodicinvestments.html
This can be a good example of how the financial model is useful when it comes to particular computations of a different big amount of financial terms. Good thing that there are also called the SaaS Financial Model who gives us good learning about choosing the financial terms.
ReplyDelete