Excel Forum
Close Window   
Free Ebook
Get Your Free Excel
Top 15 Excel Tutorials
Instant Access!

Present Value Of A Perpetuity

Present value is defined as K/i where K is the annual cash flow and i is the discount rate.

How would this formula change if the cash flow happens lets say evey 8 years in prepetuity (instead of every year). Thank you.

Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

Similar Excel Tutorials

PV Function - Get the Present Value in Excel
The Present Value (PV) function in Excel will return the current value of an investment.  This calculates the curr ...
RATE Function - Calculate an Interest Rate in Excel
The Rate function in Excel (RATE) calculates the interest rate for a financial transaction, such as for an annuity ...
FV Function - Get the Future Value in Excel
The Future Value function (FV) in Excel will return the future value of an investment based on a particular intere ...
NPER Function - Calculate Number of Periods Needed for a Set of Payments in Excel
How to calculate the number of periods required for an investment in order to get the desired return.  The number o ...

Helpful Excel Macros

Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Highlight Every Other Row in a Selection in Excel - Table Formatting
- This free Excel macro will highlight every other row in a selection of cells with a color that is specified within the m
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li

Similar Topics

I am trying to copy balances from one worksheet to another over a total of 12
worsheets ..... this is for petty cash monthly sheets and I need a running
total over the 12 sheets.

At present I have to do this manually .... is there a formula that I can use
that will automatically carry the balances over

Thanks for your help

I use Excel 2000
I have sheet muster for my clients of January, 2005 like :
( P = Present, A=Absent )
Days Sun Mon Tue Wed
Date 1 2 3 4

1 John P A A P
2 Lucy A P P A
Now I want to calculate the total Sundays, Mondays, Tuesdays etc. present in
that month. How can I do this?
Knowldege is Power

How can I calculate a 3% anual rent increase over 15 years (3% over the last year's rent)?

I have a spreadsheet set up with an employees information, including the start date. After each year that the employee is working, he gets bumped up on the pay scale. For this reason, I only need to know how many years the employee has been working, rounded down to the nearest year.

This is what I have so far (hire date is in column B):

=((TODAY()-B4)/365)&" YEARS"

This function gives me a number with many decimal places.

I tried:

=ROUNDDOWN((TODAY()-B4)/365),0)&" YEARS"

It tells me I have too few arguments. Please help!

Very new in this board. If I post this question in the wrong forum please bear with me.
There is about 20 staffs in our department and I have made a spreadsheet with 12 tabs for each month of the year Jan-Dec. If each staff have 15 days annual leave each year and how can I make a formular that can automatically calculate the balance of annual leave and entered in the tabs of the following months. For example if staff A used 5 days in Jan how can the balance of 10days be entered in the tabs from Feb-Dec. If he use another 5 days in July the remain balance of 5 days be automatically entered in the tab from Aug to Dec. Likewise if staff A took 2 days sick leave in Jan, and 2 days in Mar how can this sick days be automatically sum up in the months after. I am doing this manually at the moment and it is very time consuming and also very easy to make mistake.
Thanks and sorry for this long questions

This formula is supposed to calculate difference between today and date hired to give years and months of service. Works fine for all except if the person was hired in year 2000 then this formula shows 110 y. Any one know how to fix this? or have a different formula that works.

=DATEDIF(E21,TODAY(),"y")&" y "&DATEDIF(E21,TODAY(),"ym")&" m"


I am trying to filter a list of records by the value in a cell and then (for the filtered result(s)) change the value in column Y just for the filtered results. This is the code I have at present which filters the records OK but I can't fathom what I need to do to ensure I only chnage the value of the cell(s) in column Y?

[CODE]ActiveSheet.Range("$A$1:$DC$5000").AutoFilter Field:=1, Criteria1:="GI255"
'After filtering I want to change the value in column Y for only the filtered entries CODE]

Entering time values in custom format [h]:mm:ss
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell - then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).

Hello everyone.

This is marc, i have one doubt in my mind. that was how to create automated attendance sheet with login name. i.e. there are 25 employees working in particular corporate sector. but HR or time keeper doesn't know who are present and absent. so if it is possible to create a login sheet which directly pop up the attendance in another sheet for the current date for particular employee. please help me. Thank you in advance,


Hey all,

Coffee hasn't hit my brain yet this morning and I cant figure this one out.

I have a list of names that are in the following format:

LastName Suffix, FirstName MI

The middle initial is not always present, neither is the suffix. For simplicity purposes, we will assume the suffix can ONLY be "Jr."

Here is some sample data:

Brown Jr., Wayne
Fowler, Michael
Parker, David A
Winstead Jr., Johnny N

I want the output to be the following:

Brown, Wayne
Fowler, Michael
Parker, David
Winstead, Johnny

Thanks in advance for the help!

PS (if i can get a formula that at least gets rid of the middle initial at the end (if it exists), then that would be a HUGE step in the right direction, as the Jr. only occurs on maybe 3% of my data. The middle initial is in about 80% of the data.

I automating a task at work that will save some time. Seems simple enough but for some reason the code I have to cause IE to wait unil ready doesnt seem to work in all cases.

Im using :

'wait until the page loads
Do While ie.Busy Or ie.readyState READYSTATE_COMPLETE

This doesnt seem to work well enough, since actions are occuring that should NOT occur until the browser is ready.

Any other ideas as to how to make IE wait until the page is completely ready? I wondered about possibly checking the .innerHTML to verify that a unique page element is present.



Hello from sunny Madrid

I have created a complex formula to help me calculating Golf handicaps for players.

The formula is in the first cell and I want to drag the formula across to the right to cover the 18 holes.

But ALL the values change and I want some to change and some to stay constant.

How do I tell excel to drag the formula across but keep certain parts of the formula the same..ie ....=D11.... in the formula when dragged across doesn't go D12,D13;D14 in each cells formula and should stay as D11 for all.

Many thanks for your help


I'm trying to figure out what the formula is to multiply the total hours worked per week by their hourly pay for each individual. kind of like the
=Sum(I25:I32) Formula but I want to multiply the outcome of that formula by the hourly rate of 11.00, 15.00 and 16.00. Does anybody know how this can be done? If so I would greatly appreciate it.

I see a formula that our Human Resources Department is using to annualize voluntary turnover.

It looks like this:


So if I substitute with some actual data:

=(6/((22+33)/2))*12/months the answer becomes 21.8% (as the annualized voluntary turnover rate).

What does the *12/months actually do mathematically? I cannot find it in the excel help files and have not been able to figure this out using math (on the calculator).

Ok, I'm going to try and explain this. I am going to have 5 data points in cells such as A2:A6

All I need is to know if there is an upward trend or downward trend of the numbers.... I can't have something that simply takes the first and last number and checks if it's higher or lower. I need to actually see if they are trending upwards. Also how would i go about making it so that if they are trending upwards it shows an up arrow, and down arrow for downwards...

And finally, if the trend continues at the same rate, how long it will take the number to reach a goal. Each data point will be 1 week apart, with an ultimate goal needed to be achieved at the end.
This will be a completely separate formula on the sheet....

Any help?

I need to create a bar chart for the year. I've already have 3 bars for each of the months, a plan, an actual, and a last year. I need to be able to split out just the actual bar, so that it is a stacked bar. The other two bars need to stay and remain unstacked. Is this possible?

A previous poster explained what I also need; there were no responses,
so I thought I would try again.

Does anyone know of an Excel template--or a small stand-alone
program--that will calculate the interest/principal breakdown when
payments are varied in amount and frequency? Free or low-cost, please.
I need one that will work on my Mac/Office 2004. I will need to print
out periodic reports.

Here is the way the previous poster described it:
"Excel template: Loan Amortization for random/irregular payments,
figures days
between payment dates.
I have a loan with a variable beginning balance and irregular payments
annual large payment. (based on collections)
Would like to enter payment and date.
then Excel would figure days since last payment, interest amount,
amount, Ending Principal balance.
And total interest paid, total pricipal paid
If I change the starting principal, excel would recalculate all

Thanks very much.

Is there a way to lets say go to www.gmail.com and automatically have VBA fill in the user name and password ? I am trying to automate login for my co workers to a certain site. Just havent been able to find out how. PLZ HELP !!

I want to create 6 numbers from 11 numbers..
lets suppose my numbers a 1,2,3,4,5,6,7,8,9,10,11
I want to generate all possibilities of the 6 numbers. But i dont want duplicate series.. I mean 1,2,3,4,5,6 and 6,5,4,3,2,1 are duplicates.


I have noticed that a handful of people at work enter a formula as =+A1+A2 instead of the way I would enter it =A1+A2

What is the origin of the extra plus sign at the beginning? To me it seems to be unproductive and is simply not needed. I have been using Excel for over 12 years and have never needed to enter any formulas like that. Does this originate back to a much older version of Excel where it was once needed?

Just curious.

I have a spreadsheet where I need to calculate the increase in % one year over the other in column D for Eg if Profits are 95415 in Jan 2008 (Column B) and 80215 in Jan 2008 (Column C), then the % Increase will be (95415-80215)/80215 *100

I can calculate the formula for this , but the problem comes where for eg there is a loss in Jan 2008 for -95415 and there was a profit in Jan 2007 for 80215. How do I set up formula to take negatives & positives into account in calculating the increase or decrease/

Your assistance or anybody's on the forum will be greatly appreciated





How do I enter the value in a cell (say B4) into a formula as a constant so it doesn't change when I copy it down the column?

For example,

B4 = 2.4 (a constant)

This is the result I want:

F6 =C8 + B4*4.243

F7 =C9 + B4*4.243

F8 =C10 + B4*4.243

B4 remains constant as I copy F6 down.

What's the correct formula for F6 so I can copy it down and keep B4 fixed?



Try this...

There are some valid datas in the cells A2 (Eg. 100), B2(Eg. 200), C2 and D2.
Data validation is used in cell K2 to limit it to a set of values.

Can we implement this formula?

A2=A3; B2=B3; C2=C3; D2=D3; K2=K3;
no change to any values.

Thanks in advance...

Hello everybody,

We all know we can change the source of a link of the workbook by clicking Edit>Links>Source>change source but that will change the source of all workbook formulas which are linked to a certain file, what about if i need to change the source of a single sheet? is this possible?

I appreciate your support.Thank you !