Excel Finance

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.

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 non-mortgage 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 five-year 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 stop-loss 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 day-trading returns?

What do these statistics suggest about the risk of day trading?

Answer to question sixteen the day trading problem is here:

Question Seventeen:   Currently, low-income undergraduate students can take out a total of $31,000 in federal student loan.  Subsidized student loans are only available to people in low-income 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:

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


  1. 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.

  2. Hey just wanted to give you a quick heads up. The text in your content seem to be running off the screen in Internet explorer. I’m not sure if this is a formatting issue or something to do with browser compatibility but I figured I’d post to let you know. The style and design look great though! Hope you get the issue solved soon. Thanks получить займ

  3. it is always easy to find good ski resorts online, but most of them are expensive but they are great anyway` California mortgage rates

  4. It’s amazing in support of me to truly have a web site that is valuable meant for my knowledge. sherry treppa

  5. I needed to thank you for this incredible read!! I unquestionably adored each and every piece of it. I have you bookmarked your site to look at the new stuff you post. free stock course

  6. Hmm!! This blog is really cool, I’m so lucky that I have reached here and got this awesome information.
    Poor Credit Mortgage

  7. Wow. I am definitely going share this with a few of my friends. Very cool information.
    mortgage broker for refinance in Ontario

  8. In the event that decline the relational word "communist" in the meaning of finances, we may state, that despite everything it keeps reality.Lucrotrade

  9. The next time I read a blog, I hope that it doesnt disappoint me as much as this one. I mean, I know it was my choice to read, but I actually thought you have something interesting to say. All I hear is a bunch of whining about something that you could fix if you werent too busy looking for attention. cheaploansdirectory.com

  10. I really appreciate the kind of topics you post here. Thanks for sharing us a great information that is actually helpful. Good day! credit card hbl - bank islami auto ijarah

  11. If you suddenly need cash, you can discount the gift certificate to us and use it for cash. Cultural gift certificates are Culture Land, Happy Money, and Book Culture Gift Certificates.  소액결제현금화

  12. This comment has been removed by the author.

  13. This is the sort of information I’ve long been in search of. Thanks for posting this information. device mockup

  14. You’ve made various nice points there. I did specific search terms around the matter and found mainly individuals will believe your site macbook mockup

  15. I got what you mean , regards for posting .Woh I am pleased to find this website through google. cell phone mockup