SNHU Monthly Mortgage Payment and Mortgage Size Excel Worksheet

User Generated

yfunec1954

Business Finance

Southern New Hampshire University

Description

a completed personal financial plan that integrates your previous financial plan submissions from Modules Three and Five, as well as retirement planning and funding of your needs through investments in mutual funds or equities. This last assignment requires you to once again revise your personal financial plan to reflect your cumulative learning in the course.

Now that you have expanded your knowledge regarding long-term, asset purchase planning and short-term planning, as well as retirement planning, do you have any concerns regarding health? How would these change your financial plan?

Worksheet for Funding Your Retirement Needs: Complete the attached spreadsheet (s) (which accompany your learning resource). The first entails planning for retirement. You will likely identify a gap between your personal financial goals for a home, vehicle, and retirement planning versus the availability of your present resources. The objective is to develop an investment strategy, as a suggestion, with stocks of your choosing and/or mutual funds that support your future financial needs.

Develop stock investment portfolio: Select five key stocks and determine their expected return from finance.yahoo.com. Presume you would invest 20% equally in these stocks and average the return. Calculate how much you would have to deposit each year until retirement to fund all of your initiatives assuming the rate of return on these equities is constant.

Review the amount you currently are able to set aside for this purpose. If you still identify a gap, identify how you will mitigate that risk of realizing the appropriate amount of wealth to support your personal initiatives and expectations. Propose a plan and describe your personal alternatives for bridging this gap.

Submit the completed spreadsheets to your instructor. In the Your Personal Financial Goals Worksheet, summarize and describe your intentions. Mention each spreadsheet, specifically, and indicate how the purchase of these new assets and investments will affect your prior plan submitted during Modules Three and Five. Describe your opportunities and challenges. Will you have to make changes? Indicate how you might revise your plans.

Note: If you do not wish to disclose personal financial information to your instructor, please note to change your information or provide an alternative name with the information realistically portrayed. In doing so, the experience of preparing a financial plan for another individual will prepare you to accurately complete your own plan when you are ready. Also, do not disclose the information of an individual other than yourself; remember to keep private information private.

For additional details, please refer to the Final Project Guidelines and Rubric document.

Unformatted Attachment Preview

W 11 WORKSHEET Worksheet for Calculating the Maximum Monthly Mortgage Payment and Mortgage Size for Which You Can Qualify Method 1 Determine Your Maximum Monthly Mortgage Payment Using the Ability to Pay, PITI Ratio. a. Monthly income (annual income divided by 12) b. Times 0.28: Percentage of PITI (Principal, interest, taxes, and insurance) to your monthly gross income that lenders will lend in the form of a mortgage loan (multiply line a by 0.28) x 0.28 c. Less: Estimated monthly real estate tax and insurance payments d. Equals: Your maximum monthly mortgage payment using the 28% of PITI ratio To Determine the Maximum Mortgage Loan Level Using the Maximum Monthly Mortgage Payments as Determined Using the PITI Ratio (line d): Step 1: Monthly mortgage payment for a $10,000 mortgage with a __10__ year maturity and a __4.0__% interest rate (using Table 8.1) Step 2: Maximum mortgage level = maximum monthly mortgage payment (line d) divided by the monthly mortgage payment on a $10,000, ____%, ____year mortgage (step 1 above) times $10,000 = (line d/step 1) x $10,000 $5,833 = = $1,633 $501 $1,132 = 101.25 = $111,835 Method 2 Determine Your Maximum Monthly Mortgage Payment Using the Ability to Pay, PITI Plus other Fixed Monthly Payments, Ratio. e. Monthly income (annual income divided by 12) f. Times 0.36: Percentage of PITI + current monthly fixed payments to your monthly gross income that lenders will lend in the form of a mortgage loan (multiply line a by 0.36) x 0.36 g. Less: Current nonmortgage debt payments on debt that will take over 10 months to pay off and other monhtly legal obligations such as child support and allimony payments h. Less: Estimated monthly real estate tax and insurance payments i. Equals: Your maximum monthly mortgage payment using the 36% of PITI + other fixed Monthly payments ratio (line f - g - h) To Determine the Maximum Mortgage Loan Using the PITI Plus Other Fixed Monthly Payments Ratio (line i): Step 1: Monthly mortgage payment for a $10,000 mortgage with a __10__ year maturity and a _4.0___% interest rate (using Table 8.1) Step 2: Maximum mortgage level = maximum monthly mortgage payment (line i) divided by the monthly mortgage payment on a $10,000, ____%, ____year mortgage (step 1 above) times $10,000 = (line i/step 1) x $10,000 $5,833 = $2,100 - $0 $501 = $1,599 = 101.25 = $157,926 Method 3 Determine Your Maximum Mortgage Level Using the "80% of the Appraised Value of the House" Rule. j. k. l. m. Funds available for the down payment and closing costs Less: Closing costs Equals: Funds available for the down payment (the 20%) Times 4: Maximum mortgage level using the "80% of the appraised value of the house" rule (the 20% down, line l, times 4 equals the 80% you can borrow) Conclusion: Maximum Mortgage Level for Which You Will Qualify (the lowest of the amounts using method 1, method 2, or method 3) x4 = $136,000 $102,000 $34,000 = $136,000 = $111,835 ly Mortgage n Qualify Worksheet 5 - Simplified Income Statement Worksheet Your Take-Home Pay A. B. C. Total Income Total Income Taxes After-Tax Income Available for Living Expenditures or Take-Home Pay (line A minus line B) A. - B. $350,000 $105,000 = C. $245,000 + D. + E. $10,000 $2,000 Your Living Expenses D. E. F. G. H. I. J. K. L. Total Housing Expenditures Total Food Expenditures Total Clothing and Personal Care Expenditures Total Transportation Expenditures Total Recreation Expenditures Total Medical Expenditures Total Insurance Expenditures Total Other Expenditures Total Living Expenditures (add lines D through K) + + + + + + F. G. H. I. J. K. = L. $1,800 $550 $800 $100 $550 $600 16400 C. Total Available for Savings and Investment After-Tax Income Available for Living Expenditures or Take-Home Pay C. L. Total Living Expenditures - L. $16,400 M. Income Available for Savings and Investment (line C minus line L) = M. $228,600 $245,000 W 3 WORKSHEET Balance Sheet - Calculating Your Net Worth Assets Cash Checking Savings/CDs Money Market Funds Other Monetary Assets A. Monetary Assets Value + + + + A. = Mutual Funds Stocks Bonds Life Insurance (cash-value) Cash Value of Annuities Investment Real Estate (REITs, partnerships) Other investments B. Investments B. $800 $30.000 $10.000 $50.000 $90.800 + + + + + + = $6.000 $30.000 $40.000 $355.000 $7.000 $15.000 $38.000 $491.000 401(k) and 403(b) Company Pension Keogh IRA Other Retirement Plans C. Retirement Plans + + + + C. = $60.000 $70.000 $15.000 $7.000 $85.000 $237.000 Primary Residence 2nd Home Time-Shares/Condominiums Other Housing D. Housing (market value) + + + D. = $40.000 $55.000 $7.000 $15.000 $117.000 Automobile 1 Automobile 2 Other Automobiles E. Automobiles + + E. = $14.000 $17.000 $15.000 $46.000 Collectibles Boats Furniture Other Personal Property F. Personal Property + + + F. = $2.000 $5.000 $2.500 $1.800 $11.300 Money Owed You Market Value of Your Business Other G. Other Assets + + G. = $7.500 $150.000 $35.000 $192.500 H. Total Assets (add lines A through G) H. = $1.185.600 W 3 WORKSHEET Balance Sheet - Calculating Your Net Worth (continued) Liabilities or Debts I. Current Bills (unpaid balance) Value I. = Visa Master Card Other Credit Cards J. Credit Card Debt + + J. = $0 First Mortgage 2nd Home Mortgage Home Equity Loan Other Housing Debt K. Housing + + + K. = $0 Automobile 1 Automobile 2 Other Automobile Loans L. Automobile Loans + + L. = $0 College Loans Loans on Life Insurance Policies Bank Loans Installment Loans Other M. Other Debts + + + + M. = $0 N. Total Debt (add lines I through M) N. = $0 Net Worth H. Total Assets N. Less: Total Debt O. Equals: Net Worth H. + N. O. = $1.185.600 $0 $1.185.600 WORKSHEET 1 - PERSONAL FINANCIAL GOALS WORKSHEET Make sure your goals are realistic and stated in specific, measurable terms. In addition, prioritize your goals and identify a specific time frame within which you would like to accomplish them. The listing below is not meant to be all-inclusive, but merely to provide a framework within which goals can be formalized. Short-Term Goals (less than 1 year) GOAL Accumulate emergency funds equal to 5 months' living expenses Pay off my bills including; my utility expenses like electricity and gas, and my insurance premiums. Pay off credit cards Purchase a lawn mower and a laptop Plan for vacation in London or another European country for one week Save up to start a small business to support a childrens home in my locality PRIORITY LEVEL DESIRED ACHIEVEMENT DATE priority one 6th Jan 2021 $4,500 Priority two Monthly. The 5th day of every month $1,400 Priority six Priority three 1st April 2021 15th Oct 2020 $900 $550 Priority four 23rd Dec 2020 $2,000 Priority five 1st feb 2021 ANTICIPATED COST $10,000 Intermediate-Term Goals (1 to 10 years) Save funds for college for an older child Save for a major home improvement Save for a down payment on a house Pay off outstanding major debt Finance very large items (weddings) Purchase a vacation home or time-share unit Finance a major vacation, most probably Africa, Maasai Mara Expand my business according to the profits gathered Priority one Priority three Priority two priority four priority eight 1st June 2028 1st Jan 2026 15th July 2023 3rd March 2022 5th March 2025 $20,000 $100,000 $15,000.00 $25,000 $30,000 Priority five 3rd August 2030 $120,000 Priority six 5th March 2023 priority seven 15th March 2022 $700 $150,000 Long-Term Goals (greater than 10 years) Save funds for college for a young child Purchase a second home for retirement Create a retirement fund large enough to supplement your pension so that you can live at your current standard priority one priority two 1st Jan 2040 3rd March 2045 $50,000 $160,000 priority three 5th Jan 2035 $500,000 Take care of your parents after they retire priority four begin imediately after they retire $150,000 expand my business further priority five start my own childrens home to accumulate atleast fifty homeless children and orphans. priority six by January 2036 $150,000
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

At...


Anonymous
Awesome! Perfect study aid.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags