I'm a bit new to finance, and I'm trying to figure out how to setup amortization schedules for the company I work for.

How can I calculate the number of payments (monthly) remaining dynamically, given the start date in a cell, the total number of payments in another cell, and today's date in a third cell using =TODAY()?

I did tons of searches on Google and Yahoo!, but everyone wants money to provide the answer. I know it's a simple task, as a friend whom works as a QA Engineer at Microsoft - Excel division told me it's not that hard. I read through the date functions here:

http://office.microsoft.com/en-us/ex...me%20functions

Still trying to figure it all out in my head. It's funny that I studied tons of computer languages and finance, but I can't figure out this one simple little piece of logic.

Please note that since these are amortized leases, the information will not be the same using simple PV/FV/Annuity formulae, as the payments remain fixed.

Thanks in advance for any help!

How can I calculate the number of payments (monthly) remaining dynamically, given the start date in a cell, the total number of payments in another cell, and today's date in a third cell using =TODAY()?

I did tons of searches on Google and Yahoo!, but everyone wants money to provide the answer. I know it's a simple task, as a friend whom works as a QA Engineer at Microsoft - Excel division told me it's not that hard. I read through the date functions here:

http://office.microsoft.com/en-us/ex...me%20functions

Still trying to figure it all out in my head. It's funny that I studied tons of computer languages and finance, but I can't figure out this one simple little piece of logic.

Please note that since these are amortized leases, the information will not be the same using simple PV/FV/Annuity formulae, as the payments remain fixed.

Thanks in advance for any help!

- Calculate number of payments (using NPER)
- Formula to calculate number of days & ignore blank cells
- Formula to calculate number of days & ignore blank cells
- Help with making formula to calculate number of days
- Calculating number of months in excel
- Amortization Schedule: Auto Update Based on Loan Period & Number Of Payments Per Year
- Calculate number of years from date
- Calculate number of rows with data
- Calculate number of working days
- How do I calculate number of colored cells?
- Calculating number of instances occuring in an hour for 24 hours
- Calculate number of business days between the 2 dates
- Counting the number of the remaining cells (yet to be filled in) of a column
- To calculate number of days including start date
- Calculate number of years
- Calculating number of days in leap year
- Calculating number of periods when payment amount changes
- Calculate number and amount of payments
- Forecasting number/amt of payments
- Calculate Number Of Working Days Between 2 Dates
- Calculate number of subsequent worksheets and copy data to them?
- Calculate number of days using sumproduct
- Calculate Number Of Hours By Conditions
- Calculate number of rows used on Sheet/Worksheet

I am trying to use the function NPER to calculate the number of payments it would take to eliminate some debt using a few constants.

In the NPER formula, I have the following

RATE = 12%/12 (12% APR, 12 months in a year)

PMT = 60

Pv = 3000

Fv = 0

The results of the formula = -40.7

However, when evaluating it by creating an amortization table, the number of periods requires 70 months to pay the debt off. The way I created the amortization table is by taking:

Debt = A1

Rate = B1

Payment = C1

Cell A2 = A1+A1*B$1$/12-C$1$

Cell A3 = A2+A2*B$1$/12-C$1$

Cell A3 is copied down to A4, A5, A6, A7, and so forth until the last cell has a starting balance of 0 or negative.

Is my amortization table correct, or is NPER populated wrong?

Thanks for any help you can give me.

Alex in Memphis, TN

I've set up a spreadsheet for someone else that calculates the number of

days worked by temporary employees. When I initially set it up, I thought I

just needed a column for start dates and a column for end dates, but the

person I have set it up for also wants a number of dates worked up to now

column.

I'm very new to this so realise that the way I have done it is probably

quite clumsy.

My original formula in C (days worked) was

=IF(OR(ISBLANK(A1),(ISBLANK(B1))),"",(B1-A1)) where A=start date and b=end

date. I used isblank so that C would remain empty if A and B were blank.

My problem is how do I amend this formula so that if I added an extra column

(today's date) C(days worked) would say stay blank if A is empty if B(end

date) is empty calculate number of days worked so far but if B(end date) has

a date then calculate how many day worked altogether.

Hope this isn't total gobbledygook.

Many thanks

Mifty

--

Mifty

I've set up a spreadsheet for someone else that calculates the number of

days worked by temporary employees. When I initially set it up, I thought I

just needed a column for start dates and a column for end dates, but the

person I have set it up for also wants a number of dates worked up to now

column.

I'm very new to this so realise that the way I have done it is probably

quite clumsy.

My original formula in C (days worked) was

=IF(OR(ISBLANK(A1),(ISBLANK(B1))),"",(B1-A1)) where A=start date and b=end

date. I used isblank so that C would remain empty if A and B were blank.

My problem is how do I amend this formula so that if I added an extra column

(today's date) C(days worked) would say stay blank if A is empty if B(end

date) is empty calculate number of days worked so far but if B(end date) has

a date then calculate how many day worked altogether.

Hope this isn't total gobbledygook.

Many thanks

Mifty

--

Mifty

I have tried

= IF(NETWORKDAYS(TODAY(),G11,Holidays)>0,,"0")

but I know that is not right. Please any help would greatly be appreciated.

and what would be entered into the sheet

example?

LEASE A

start date 12/26/2009

Term : 44 months

I would like something that will tell me the number of months remaining on this lease , according to my calculations there should be 30 months remaining . what is the easist way to do this and just drag down the formula

further more, if the lease is expired or has less than 6 months left i would like it to return "expired"

Any help would be appreciated~ thanks

I have uploaded a sample amortization schedule. Pls refer to that.

1. I require the table to adjust itself based on the loan period and number of payments per year entered in D14 and D15 respectively.

2. Also, if a value is entered in column E, then i require the whole table to update as well.

thanking you

Suju

For example:

Column A = 8/18/1997

Column B = NOW() which is displaying the current date ie 07/10/08

Column C = the number of years based on a formula which I don't know how to write

Any suggestions.

whether the number of rows filled (of column A) with data is 3 or more.

msg hello is printed.

is this syntax correct?

Code:

If workbook2.Sheets("Sheet1").CountA(A) = 3 Then Msgbox "Hello"

weekdays (have manually entered my desired results below).

have googled the last hour and cannot find the formula i need. btw i

will be sending this spreadsheet to people without the analysis

toolpack so cannot use networkdays function.

start date end date # working days

Sun 01 May Sun 01 May 0

Sun 01 May Mon 02 May 1

Sun 01 May Tue 03 May 2

Sun 01 May Wed 04 May 3

Sun 01 May Thu 05 May 4

Sun 01 May Fri 06 May 5

Sun 01 May Sat 07 May 5

Sun 01 May Sun 08 May 5

Sun 01 May Mon 09 May 6

Sun 01 May Tue 10 May 7

Sun 01 May Wed 11 May 8

Sun 01 May Thu 12 May 9

Sun 01 May Fri 13 May 10

Sun 01 May Sat 14 May 10

Sun 01 May Sun 15 May 10

Sun 01 May Mon 16 May 11

Sun 01 May Tue 17 May 12

Sun 01 May Wed 18 May 13

Sun 01 May Thu 19 May 14

Sun 01 May Fri 20 May 15

Sun 01 May Sat 21 May 16

Sun 01 May Sun 22 May 16

I really need your help to solve one issue: how to calculate number of

collor cells?

For instance, from cells range A2:F2 (6 cells),if there are 2 cells filled

in any colors, the counter in cell G2 should be equal to 4(6-2).

Any thoughts?

--

Thanks in advance!

day for theentire year.

suppose: the computer records the name and info of a person arrived and

stores the time he/she arrived like:

10:27 xyz

10:29 abc

10:45 123

so I want to calculate the total arrivals in an hour from 10:00-10:59

and I want to that for the entire 24 hour period and then for the

entire month for each hour,

Please let me know how could i do it in a faster way using exfel.

Thanks,

RP!

I have a query, I want to calculate number of days between 2 given

dates...to be more clear i want excel to calculate only business days

monday-friday and not saturdays and sundays. Please help me...

Any help will be much appreciated

I want to calculate number of days between two dates, including start date also.

For Example

A B C (No.of days)

23/03/2012 30/03/2012 8

28/03/2012 28/03/2012 1

I dont want to use networkdays function, since it excludes weekends. (I want to include saturdays & sundays also)

right now I am using =(B2-A2)+1

Is it any other formula available.

i type this is i get a weird answer. problem is not that i get the number

signs....i get a date 1900. for example: 1/1/1900 0:00. please advise for

this is for homework, university. thank you to whomever responds.

D L Barnard

B3=01/01/2012

B4=12/31/2012

=((MONTH(B4)&"/")&DAY(B4))-((MONTH(B3)&"/")&DAY(B3))

But output is 365 for 01/01/2012 – 12/31/2012

Should be 366 bez leap year

PS: MM/DD/YY format

Pls help …

Thanks !

increase the amount I am paying by a fixed amount. For example, this year I

will pay $800. Starting next year I will pay $850, the year after that, $900,

and so on.

I know the NPER function to calculate the number of periods given a rate,

fixed payment, and present value.

How can I calculate the number of periods required to pay off the loan when

the payment is changing, assuming the timing and amount of the change are

known?

Thanks for your help.

Great website here, already borrowed one formula. Although now I have another obstacle to conquer.

The example:

Coloumn A contains dates format of 12/02/2009, but another format such as 10-Apr-09 etc could be used.

Coloumn B contains the amounts of payments received, i.e £5.00, £10.00, £20.00

Now what I require is to be display in another coloumn (say Coloumn C) the number of payments that were received last week and last month and then the total value of the payments.

So the sort of result I'm looking for would be like

Assume todays date is 19-04-09

A B C

12-04-09 £5.00 Last Week 4 Payments Value £45.00

12-04-09 £10.00

13-04-09 £10.00

14-04-09 £20.00

Thanks guys, any help greatly appreciated.

My company purchases certain items from vendors. The price for these purchases are fixed by contract. However, they occur on different dates. I am looking to forecast how much we will pay per month.

1) I would like Excel to calculate the number of payments made every month of each year in the dataset for every month from September 2010 to September 2050.

a) For example, to know how payments made September 2010, how many payments made Oct. 2010….all the way to how many payments made August 2050 and how many payments made September 2050.

b) I will not know the range of the data; it will change on every spreadsheet I receive. My supervisor does not want to manually calculate the range of the data; can excel calculate the entire range and then feed it into this formula for me?

2) I would like Excel to then sum how much would be paid each month.

a) If a certain vendor is paid for a month, we will pay the price equal to the “price” variable to that vendor.

b) I would like to sum the amount paid to all the vendors we purchase from every month.

Attached is an example worksheet showing the type of data I have. I showed the numbers I would like to be automatically summed. Let me know if this is unclear.

I am sure this is probably very easy; I am not very experienced in excel

Thank you very much.

GQuinn

I am using the below formula to calculate the number of days between dates, and it also calculates how many days to the current day, my only problem is if I type 24/04/12 in the column it shows as 2 days when in fact it is only 1 day, does anyone know how to modify to incorporate this.

=IF(D38"",NETWORKDAYS(B38,D38),NETWORKDAYS(B38,TODAY())) This is in Column E (Working Days)

A B C D E

Date of Meeting Master Release Reqested Time Received Working Days 06/01/12 24/04/12 12:00pm 2

Working days should read 1

Any help is greatly appreciated.

Shazz

I do enjoy a programming challenge and would love to be able to answer this one on my own but sadly, I cannot.

I have a workbook called 'May'.

It contains 31 worksheets entitled '1st', '2nd', '3rd etc - up to '29th', '30th' and '31st' and these correspond with the dates in that month.

The same row of each worksheet contains the names of employees. (About 18).

The user will manually complete the names in the appropriate row and columns of the '1st' worksheet.

I'm trying to find a macro/function/formula to copy that row of employees names to all the subsequent days (sheets), and ideally this would be activated by a user with a macro assigned button. (I can take care of the easy part - designing the button and assigning the macro).

The macro will initially be used on the 1st of the month to copy the names all the way through to the 31st. This is ideal if there is to be no change in the employees, but a user will need the facility to amend the list on any day throughout the month up right through to the end of the month.

(e.g. on the 14th of the month a member of staff leaves and needs to be removed from the list from the day after they left until the 31st. So.. The user amends the row of names to reflect the now absent staff member and uses the macro to re-populate the subsequent worksheets.

Then, on the 16th they are replaced and the new member of staff has to be added from 16th all the way through to the 31st)

My aim is to make the process as easy as possible for the user and to avoid as much repetition as possible.

The macro will need firstly to establish the remaining number of sheets from the date the amendment is made and then populate the appropriate rows of those sheets with the selected employees names.

I really hope I've made sense in my explanation and I'm very grateful to you for reading and considering.

So many thanks to you, Gordon.

I would like to

1) check if the value in column g = "In Progress"

2) calculate the number of days the "In Progress" task has been opened using the date in column d based on company a, b or c from column f

3) return the count based on 3 different ranges (>0 and < 30, >30 and 60)

I am not sure if I can modify a sumproduct command something like

sumproduct(all company "a" with status "in progress" and calculate it as it goes)

I currently have it set up this way

1. column I calculates the days the task has been opened

VB:2. column k then breaks it into the 3 different headings(G8="In Progress",TODAY()-D8," ")If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:3. sumproduct then calculates all the entries for the companies based on all the entries in column k0"))))If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have over 6000 entries so dont want to have to use formulas in every cell which slows it down etc.

So, I created a drop down list for them to select their names, and a drop down list to select the programs that they taught.

I would like a formula that looks up whenever an Instructor teaches and can calculate the number of hours they taught each class. And, the total number of hours they taught.

Ex.

A1 B1 C1

Bugs PALS 3 hours

Harry PALS 6 hours

Sally BLS 4 hours

Jim BLS 5 hours

Jim ACLS 2 hours

Sally BLS 3 hours

Sally PALS 1 hour

So, based on above, the formula would be able to calculate that Sally taught 1 hour of PALS. Sally taught 7 hours of BLS. And, in total she taught 8 hours.

Is there anyway that I can do this?

first can i know or calculate the number of rows used in sheet. for exa: i have A1:A100 i want that B1=100 and if i delete some of rows the number of rows in B1 auto-change. and can let B2 display the number of rows deleted.

i have 2 sheets now, can the Cell C1 in sheet 2 display the number of rows in the sheets, B1 in sheet1 + B1 in sheet 2

tnx,

No luck finding an answer? You could always try Google.