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 2 hours agoRelated Solutions
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...
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: _____________
I have some euros in my bank account (Currently: euro = $1.50). I also bought a...
I have some euros in my bank account (Currently: euro = $1.50).
I also bought a put option on euro (X=$1.50, Premium=$0.02). a.
Draw the combined graph. b. What is the name of this strategy? What
is the objective with this strategy? c. How many break even points
are there? What is (are) the break even point(s)? d. If
ST=$1.35/euro just before expiration, what is my profit? e. If
ST=$1.60/euro just before expiration, what is my profit?
Determine the amount of money in a savings account at the end of 8 years, given...
Determine the amount of money in a savings account at the end of 8
years, given an initial deposit of $40,000 and a 4 percent annual
interest rate when interest is compounded:
1.
Annually
2.
Semiannually
3.
Quarterly
The amount of $5000 is placed in savings account where interest is compounded continuously at the...
The amount of $5000 is placed in savings account where interest
is compounded continuously at the rate of 6% per year. How long
will it take for this amount to triple?
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- Axtel Company has the following financial statements. Axtel Company Balance Sheet For the period ended 12/31/X1...
- Explain how Morrison Pump Company could implement an activity-based costing (ABC) system. When you are deciding...
- Part 1) Discuss the relationship between hospitals' profitability and their performance at managing two components of...
- A three-phase line has an impedance of 1 + j3 Ω per phase. The line feeds...
- Bergman Corp. has experienced zero growth over the last seven years, paying an annual dividend of...
- As the controller of Lynbrook Securities, Inc., you were asked to evaluate a potential bond issuance...
- How has technology affected the role of the supply chain professional? Is it a positive or...
ADVERTISEMENT