Question
In: Finance
I and my husband Jatin have total amount of $150,000 in our savings account. We have...
I and my husband Jatin have total amount of $150,000 in our savings account. We have 3 school going kids. We want to buy a new home, a new car and keep funds for children higher education.
We finalized to buy a home for $760,000. We may use $120,000 of our savings as a down payment on it. For balance financing the mortgage specialist/agent gave us the following options:
- Option 1: a 25-year mortgage/loan, with semi-monthly payments (at the end of each period). The interest rate on the mortgage is 3.26% APR (annual percentage rate) compounded semi-annually.
Ques 1. What will the semi-monthly payment be on the Option 1 mortgage?
Please use (display + name) the excel function/ formula used for each yellow cell.
|
1. option 1 Mortgage loan |
|
|
annual rate |
3.26% |
|
period rate |
|
|
loan amount |
|
|
#periods |
|
|
semi-monthly payment: |
- Option 2: a monthly payment of $2,900 to be made at the end of each period. The interest rate with this option would be 3.60% APR (annual percentage rate) compounded semi-annually.
Ques 2. How many years will Option 2 mortgage be amortized over? Please use (display + name) the available excel function/ formula in each yellow cell .
|
2. option 2 mortgage |
|
|
monthly payment |
-$2,900.00 |
|
A |
3.60% |
|
period rate |
|
|
loan |
|
|
Number of years needed to pay loan: |
Ques 3. To buy a new car of $45,000 (including taxes). In exchange of our old car for $10,000 and $10,000 from our savings as a down payment, the car dealer would provide the $25,000 balance as a 5-year loan paid semi-monthly at 4.8% ANNUAL RATE compounded semi-monthly. What will the payment be on the loan for the car as per below information? Please use (display + name) the excel function/ formula used for each yellow cell.
|
Answer 3. car loan |
|
|
ANNUAL RATE |
4.80% |
|
period rate |
|
|
loan |
|
|
#periods |
|
|
semi-monthly payment |
Solutions
Expert Solution
| 1 | Option1 Mortgage Loan | |||||||||||
| Annual Rate | 3.26% | |||||||||||
| Semi annual rate =(3.26/2) | 1.630% | |||||||||||
| Period Rate (Semi monthly)=r | ||||||||||||
| Number of periods in 6 months | 12 | (6*2) | ||||||||||
| (1+r)^12=(1+0.0163) | ||||||||||||
| 1+r=1.0163^(1/12)= | 1.00134829 | |||||||||||
| Period Rate (Semi monthly)=r= | 0.00134829 | |||||||||||
| Rate | Period Rate (Semi monthly)=r= | 0.1348% | ||||||||||
| Nper | Number of Periods of Mortgage | 600 | (25*12*2) | |||||||||
| Pv | Loan Amount | $640,000 | (760000-120000) | |||||||||
| PMT | Semi -Monthly Payment | $1,556.35 | (Using PMT function of excel with Rate=0.1348%,Nper=600,Pv=-640000) | |||||||||
| 2 | Option2 Mortgage | |||||||||||
| Pmt | Monthly Payment | ($2,900) | ||||||||||
| Annual Rate | 3.60% | |||||||||||
| Rate | Period (Monthly ) Rate=3.6/12= | 0.3% | ||||||||||
| Pv | Loan amount | $640,000 | ||||||||||
| NPER | Number of Months to Pay | 362.180001 | (Using NPER function of excel with Rate=0.3%,Pmt=-2900,Pv=640000) | |||||||||
| NPER/12 | Number of Years to Pay Loan | 30.1816667 | ||||||||||
| 3 | Car Loan | |||||||||||
| ANNUAL RATE | 4.80% | |||||||||||
| Rate | Period Rate (Semi monthly)=4.8/24 | 0.2% | ||||||||||
| Nper | Number of Periods =5Year*12*2 | 120 | ||||||||||
| Pv | Loan Amount | $25,000 | ||||||||||
| PMT | Semi -monthly payment | $234.54 | (Using PMT function of excel with Rate=0.2%,Nper=120,Pv=-25000) | |||||||||
   |
||||||||||||
jeff jeffy answered 1 year agoRelated Solutions
I and my husband Jose have total amount of $150,000 in our savings account. We have...
I and my husband Jose have total
amount of $150,000 in our savings account. We have 3 school going
kids. We want to buy a new home, a new car and keep funds for
children higher education.
We finalized to buy a home for
$760,000. We may use $120,000 of our savings as a down payment on
it. For balance financing the mortgage specialist/agent gave us the
following options:
Option 1: a 25-year mortgage/loan, with semi-monthly payments
(at the end...
“My husband is 53, and I am 13 years younger. During our 20-year marriage, I have...
“My husband is 53, and I am 13 years younger. During our 20-year
marriage, I have been in and out of the workforce, raising
children, and getting my education. Now, I plan to return to
full-time employment. I am essentially just getting my career under
way as my husband approaches the completion of his. None of the
retirement seminars address the issue that not all husbands and
wives are the same age, nor do they retire at the same time.”...
Suppose you have a certain amount of money in a savings account that earns compound monthly...
Suppose you have a certain amount of money in a savings account
that earns compound monthly interest, and you want to calculate the
amount that you will have after a specific number of months. The
formula is as follows:
f = p * (1 + i)^t
• f is the future value of the account after the specified time
period.
• p is the present value of the account.
• i is the monthly interest rate.
• t is the...
The formula for calculating the amount of money in a savings account that begins with an...
The formula for calculating the amount
of money in a savings account that begins with an initial principal
value (P) and earns annual interest (i) for (n) years is: P(1 +
i)n
Note that i is a decimal, not a percent interest rate: .1 NOT
10%
Write a python program that does the
following:
Prompt the user on three lines for principal, percent interest
rate and number of years to invest (using descriptive prompts)
Use a while loop to...
I live with my mom and my 5 year old daughter. The combination of husband, wife,...
I live with my mom and my 5 year old daughter.
The combination of husband, wife, and children that 99.8 percent of
people in the United States believe constitutes a family is not
representative of 99.8 percent of U.S. families. According to 2010
census data, only 66 percent of children under seventeen years old
live in a household with two married parents."
Describe what your family unit currently looks like. (Mom, Dad,
Brothers, Sisters?) Do you all live with one...
An amount of $10,000 is deposited into a savings account that pays interest at a rate...
An amount of $10,000 is deposited into a savings account that
pays interest at a rate of 7%. If 10 equal annual withdrawals are
made from the account starting one year after the money was
deposited, how much can be withdrawn so that in the fifth year one
would be able to withdraw an additional $1,000 and the account
would be depleted after 10 years?
Explain verbally in detail and sketch a timeline to
illustrate.
I have $62,000 in my investment account. I want to add $2,000 each month into this...
I have $62,000 in my investment account. I want to add $2,000
each month into this account starting today. I am expecting to earn
1%/month. How much will I have in this account in 2 years?
*Please post formulas or calculations done by calculator*
ABC wants to have $20,000 in a savings account four years from now. What amount must...
ABC wants to have $20,000 in a savings account four years from
now. What amount must ABC deposit into an account today if the
account will earn 10% interest, and interest is compounded
semiannually?
Present Value of $1
Periods
5%
6%
8%
10%
4
0.823
0.792
0.735
0.683
5
0.784
0.747
0.681
0.621
6
0.746
0.705
0.630
0.564
7
0.711
0.665
0.583
0.513
8
0.677
0.627
0.540
0.467
9
0.645
0.592
0.500
0.424
10
0.614
0.558
0.463
0.386
Present Value...
C++ Assignment: For this assignment, we will model a checking account and savings account, arranging these...
C++ Assignment:
For this assignment, we will model a checking account
and savings account, arranging these two types of accounts within a
simple class hierarchy beneath a base class. By creating a class
hierarchy, we will avoid unnecessary code duplication across the
derived classes and create an intuitive relationship between them.
We will expand on this assignment in a future
assignment.
Create a class named Account, which will contain data
and methods that would naturally be shared by any derived...
How much do we need to have in an account to afford this if we earn 5% on our investments?
We need to spend $20,000 per year for 3 years on new restaurant
equipment. How much do we need to have in an account to afford this
if we earn 5% on our investments?__________________20,000 ________________ N_______ 1/YCPT _______Answer: _____________
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- Why does a particular codon represent only one specific amino acid?
- Write a function template sort that takes two references to generic objects and switches them if...
- What 2 things happen to the melting point of a solid when an impurity is present?
- How Can Hominin Evolution be characterized as " mosaic evolution" Use examples of species from the...
- Absorption and Variable Costing Income Statements for Two Months and Analysis During the first month of...
- Mobile and wireless devices are being increasingly used in the Business Administration and Management Industry. write...
- What are the options that might be considered in relation to introducing flexible work practices? What...
ADVERTISEMENT