From the course: Excel: Financial Functions in Depth
Unlock this course with a free trial
Join today to access over 24,700 courses taught by industry experts.
Calculate pro-rata rental costs with date functions - Microsoft Excel Tutorial
From the course: Excel: Financial Functions in Depth
Calculate pro-rata rental costs with date functions
- [Instructor] Something that we often have to do in finance is to calculate pro rata costs, and date functions in Excel can help you to do this. Let's say, for example, you needed to calculate the final month's lease fee at the end of a tenancy. So the lease end date is the 9th of April and we need to work out how much rent we need to pay for that period of nine days. So the first thing we can do is use the day function to pick up the day of that month. So it's nine days worth of rent that we need to calculate, but we need to work it out how much the rent is for each day. So to do that, we need to work out how many days there are in April, and we can do that using the EOMONTH function. So I'll say comma 0, and that will take us to the last day of the month. So I can then use the day function again to work out how many days there are in April. So if we were to pay 4,800 for a full month of rent, we can work out the daily rental rate by dividing it by 30. So for each day that we are…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Contents
-
-
-
-
-
-
-
(Locked)
TODAY, EOMONTH, EDATE, and timing flags4m 14s
-
(Locked)
Calculate pro-rata rental costs with date functions4m 9s
-
(Locked)
IF: Building logical comparisons3m 55s
-
(Locked)
Calculating the payback period4m 24s
-
(Locked)
Using RATE or RRI for compound annual growth rate (CAGR)4m 46s
-
(Locked)
Creating a debt schedule4m 36s
-
(Locked)
Using SLN and IF to calculate depreciation4m 29s
-
(Locked)
Creating a depreciation schedule5m 57s
-
(Locked)
Using dynamic arrays to create a depreciation waterfall6m 15s
-
(Locked)
Calcuating weighted average cost of capital (WACC)6m 21s
-
(Locked)
Using NPV to calculate a discounted cash flow (DCF)4m 34s
-
(Locked)
-