|
Excel Finance Trick #5: PMT & FV function & Delayed Payments
Video | Similar Helpful Excel Resources
See how to use the PMT & FV function together to calculate a delayed PMT payment amount.
See how to use the PMT function to calculate a payment amount when there are delayed payments. When you have to make Period payments on a loan contract and you are not required to make payments until sometime later use this trick to calculate the PMT amount.
See how to use the PMT & FV function together to calculate a delayed PMT payment amount.
In This Series learn 17 amazing Finance Tricks. Learn about the PMT, PV, FV, NPER, RATE, SLN, DB, EFFECT, NOMINAL, NPV, XNPV, and the CUMIPMT functions that can make your financing tasks much easier in Excel. See how to use the PMT function in the standard way, but also see how to use it while incorporating a Balloon payment or a delayed payment. Lean how to translate a Nominal interest rate into an Effective Interest rate. Learn how to calculate how long it takes to pay off a credit card balance. Lean how to calculate the Effect Rate on a Payday loan. And many more financing Tricks!!
The Excel Finance Tricks 1-17 will show an assortment of Excel Financing Tricks!
Formula
Got a Question? Ask it Here in the Forum.
Similar Helpful Excel Resources
I need to write an Excel function that returns best price of a government bond.
The actual handwritten formula is quite complicated, impossible to display
here, and the input values for this formula are-----------------
Accrued days, Number of coupon period days, Days from settlement to next
coupon date, coupon periods per year, number coupon periods between
settlement & redemption, annual yield, discount rate.
I am having an issue with a User Defined Function... I have distilled it down to this...
the function is:
Code:
Function FN(Trade As Range) As Integer
FN = CInt(Trade.Text)
End Function
pretty simple, right?
make a1 = 1; a2=1; a3=a1+a2
make b1 = 1; b2=2; b3=b1+b2
make d1=max(FN(a3),FN(b3))
d1 now = 3
so far, so good....
If I change A1 to a "3", A3 updates to "4", but D1 does not change...
now if I update A1 to 5, A3 update to a 6, and D1 updates to a 4...
Update A2 to a 6, A3 updates to an 11, and D1 updates to a 6...
It looks like the B3 cell is always one update behind.
Any thoughts? been racking my brain and burning up google for a while...
I have tried adding application.volatile... does not help and I would rather not use it if I do not have to.
thanks for any hints...
Hey everyone I am new to programing in Excel and I do not know enough to wright a function that finds variance of a portfolio. This is what i have so far its messy and does not work.
Function VARPORT(Sa,Wa,Sb,Wb,Sc,Wc,Sd,Wd,Se,We)
va=VAR(Sa)
vb=VAR(Sb)
vc=VAR(Sc)
vd=VAR(Sd)
ve=VAR(Se)
cab=Covar(va,vb)
cac=Covar(va,vc)
cad=Covar(va,vd)
cae=Covar(va,ve)
cbc=Covar(vb,vc)
cbd=Covar(vb,vd)
cbe=Covar(vb,ve)
ccd=Covar(vc,vd)
cce=Covar(vc,ve)
cde=Covar(vd,ve)
VAPORT=((Wa^2)*va)+((Wb^2)*vb)+((Wc^2)*vc)+((Wd^2)*vd)+((We^2)*ve)+(2*Wa*Wb*cab)+(2*Wa*Wc*cac)+(2*Wa *Wd*cad)+(2*Wa*We*cae)+(2*Wb*Wc*cbc)+(2*Wb*Wd*cbd)+(2*Wb*We*cbe)+(2*Wc*Wd*ccd)+(2*Wc*We*cce)+(2*Wd*W e*cde)
End Function
The S stands for a list of stock returns.
The W is what percentage of the portfolio is spent on that stock.
The Covar and Var functions are to calculate the variance of each stock and the covariance between the five.
The three letters are C for covariance and then what stock will be looked at.
This is very confusing to me and I know I just made a big mess, can anyone help or point me int he right direction.
Work four summers, starting at age 16
Save the income in a Roth IRA account
Invest it in a simple, low-cost equity portfolio
Simmer slowly for 47 years
Serve ungarnished (and untaxed) at age 67
If your money is invested in common stocks and you achieve the average
compound annual rate on large-capitalization U.S. stocks, 10.7%, your
account will grow to $9,378 at the end of the fourth year. You will be
20 years old. Invested in the same way, with no additional savings, the
account will grow to:
$25,917 by the time you are 30
$71,625 by the time you are 40
$197,943 by the time you are 50
$547,037 by the time you are 60
And $1,114,423 by the time you are 67
is there a function that will give me this answer without using
multiple rows and columns to calculate this.
Hi there,
I've set up a worksheet with a VBE-script that tells to send an email via my Outlook upon certain conditions. That works fine.
However, I want to insert a delay (i.e. 30 days) in the sent e-mail just like you can do in Microsoft Outlook in the message options dialog box by checking the "Do not deliver for ..." checkbox. How can I utilize this "Do not deliver for ..." functionality in VBA?
Hopefully you brilliant people have any suggestions
Below is my script for sending an email from excel
Code:
Sub SendMail()
Dim OutApp As Object
Dim Outmail As Object
Dim strBody As String
Set OutApp = CreateObject("Outlook.Application")
OutApp.session.logon
Set Outmail = OutApp.CreateItem(0)
strBody = "Test"
On Error Resume Next
With Outmail
.To = "test@test.com" 'change "test@test.com" into "yourownmailadres@mail.com" to make the macro really work
.CC = ""
.BCC = ""
.Subject = "Test - No Reply - Automatic mail"
.Body = strBody
.Send
End With
On Error GoTo 0
Set Outmail = Nothing
Set OutApp = Nothing
End Sub
I am running Excel 2003 on a brand new HP laptop with 2GB Ram and 250GB hard drive (which is more empty than full), running Vista Home Premium.
Excel 2003 has an long delay when clicking page preview, or setup in page preview and sometimes in just inserting rows or columns.
This didn't happen in XP.
Are there any options that can be changed in either Excel or Vista that will reduce this delayed response?
Any help would be most appreciated as I seem to live in Excel!
Hello,
We have a user that keeps getting an Write -Delayed Write Failed error. I've tried running all of the office updates for her,restoring the document, saving the document to the desktop first, and saving it back to the network. We also, tried deleting a lot of data to decrease the file size from 4.80MB. None of this worked. Does anyone know how to fix this issue?The only thing I have not tried is to run an excel repair.
This isnt your typical email request, in this case I would like to set a Calendar event for a list of users with a reminder (Series) in OutLook.
Has anyone tried this, is it possible?
Cross posted:
http://www.microsoft.com/office/comm...60b&sloc=en-us
Hey guys,
I've looked and looked and can't seem to find info on this anywhere.
I'm trying to calculate equal loan payments with an annual repayment.
However, the payments are always due on the same day (say March 15th of the following year) but the loan can be originated at any point in the year. So the time between the loan origination and the first payment most likely won't equal 1 year, but the rest of the payments will be a year apart.
So a loan is done today, August 11th 2008.
1st payment 3/15/09
2nd payment 3/15/10....
The equal payment thing is messing me up. I didn't see anything in the Excel help section....can any of you help? Thanks.
I wanted to figure how much my savings would be on a loan if I made biweekly
payments instead of monthly payments. Example: if the regular monthly
payment was $1,231 , I would make two payments of $615 instead. How can I
determine how much I will save in interest with option 2?
rate:6.25%/12
nper:12*30
loan amount:200,000
The above amounts are what I used to determine the monthly payment of $1231,
which multiplied out will have me repaying $443,160 over the life of the
loan. How much interest would I be saving making the biweekly payments is
what I want to figure out? Can anyone help?
|
|
