Cập nhật nội dung chi tiết về Cách Tính Thời Gian Trong Excel mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
Trong bài viết này, Học Excel văn phòng sẽ hướng dẫn những công thức hữu ích để cộng/trừ thời gian, chênh lệch thời gian, đếm giờ…
Cách tính chênh lệch thời gian trong Excel
Công thức 1. Trừ thời gian
Như bạn đã biết, thời gian trong Excel thường được định dạng số thập phân để trông giống thời gian. Vì chúng là số, nên bạn có thể cộng trừ thời gian như các giá trị số khác.
Công thức đơn giản nhất để tính chênh lệch thời gian là:
Tùy vào cấu trúc dữ liệu, chênh lệch thời gian thực tế có thể có nhiều dạng khác nhau, ví dụ:
=A2-B2Tính toán chênh lệch thời gian giữa hai giá trị trong ô A2 và B2
=TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”)Tính toán chênh lệch thời gian giữa hai thời gian cụ thể
=TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) – TIME(HOUR(B2), MINUTE(B2), SECOND(B2))Tính toán chênh lệch thời gian giữa hai giá trị trong ô A2 và B2, không tính chênh lệch ngày tháng nếu ô tính chứa cả giá trị ngày tháng và thời gian
Trong hệ thống Excel, thời gian biểu thị ở phần thập phân, bạn sẽ có kết quả như sau:
Để áp dụng định dạng thời gian, nhấn để mở hộp thoại Format Cells, chọn từ Category list và gõ mã thời gian vào hộp .
Bây giờ, để xem công thức tính chênh lệch thời gian và mã thời gian hoạt động như thế nào trong bảng tính thực sự. Với Start time trong cột A và End time trong cột B, bạn có thể sao chép công thức vào cột C và E:
=$B2-$A2
Thời gian chênh lệch được hiển thị khác nhau tùy vào định dạng thời gian trong mỗi cột:
Công thức 2. Tính chênh lệch thời gian bằng hàm TEXT
Một kỹ thuật khác để tính chênh lệch thời gian trong Excel là sử dụng hàm TEXT .
Tính toán chênh lệch giờ giữa hai thời gian: =TEXT(B2-A2, “h”)
Tính toán chênh lệch giờ và phút giữa hai thời gian: =TEXT(B2-A2, “h:mm”)
Tính toán chênh lệch giờ, phút và giây giữa hai thời gian: =TEXT(B2-A2, “h:mm:ss”)
Kết quả của hàm TEXT luôn là văn bản. Các giá trị text được căn lề trài trong các cột C, D, E trong hình trên. Trong vài tình huống nhất định, điều này có thể là một hạn chế vì bạn sẽ không thể sử dụng kết quả này để tiếp tục tính toán.
Nếu kết quả là một số âm, công thức TEXT sẽ hiển thị lỗi #VALUE!.
Công thức 3. Tính giờ, phút, giây giữa hai thời gian
Để hiển thị chênh lệch giữa hai thời gian bằng một đơn vị (giờ, phút, giây), bạn cần thực hiện các phép tính sau.
Phép tính giờ giữa hai thời gian =(End time – Start time) * 24
Giả sử thời gian bắt đầu trong ô A2 và thời gian kết thúc trong ô B2, bạn hãy sử dụng phép tính đơn giản là B2-A2 để tính toán chênh lệch thời gian, sau đó nhân với 24.
=(B2-A2) * 24
Để có số Complete hours, sử dụng hàm INT để làm tròn đến số nguyên hần nhất:
=INT((B2-A2) * 24) Tổng số phút giữa hai thời gian:
Để tính số phút giữa hai thời gian, nhân chênh lệch thời gian với 1440
=(End time – Start time) * 1440
Công thức có thể cho kết quả âm hoặc dương, kết quả âm xảy ra khi thời gian kết thúc lớn hơn thời gian bắt đầu như hàng 5:
=(B2-A2)*1440 Tổng số giây giữa hai thời gian:
Để tính số giây giữa hai thời gian, nhân chênh lệch thời gian với 86400
=(End time – Start time) * 86400
Trong ví dụ, công thức sẽ như sau:
Công thức 4. Tính chênh lệch thời gian trong cùng một đơn vị thời gian và bỏ qua các đơn vị khác
Để tính chênh lệch giữa hai thời gian trong cùng một đơn vị, bỏ qua các đơn vị thời gian khác, hãy sử dụng một trong các hàm sau:
Chênh lệch giờ, bỏ qua phút và giây:
=HOUR(B2-A2)
Chênh lệch phút, bỏ qua giờ và giây:
=MINUTE(B2-A2)
Chênh lệch giây, bỏ qua giờ và phút:
=SECOND(B2-A2)
Khi sử dụng hàm HOUR, MINUTE, SECOND, hãy nhớ rằng kết quả không được vượt quá 24 giờ, 60 phút hoặc 60 giây.
Công thức 5. Tính thời gian trôi qua kể từ thời gian bắt đầu đến bây giờ
Để tính thời gian đã trôi qua, bạn có thể sử dụng hàm Now và Time. Trường hợp khoảng thời gian trôi qua dưới 24 giờ, bạn chỉ cần sử dụng hàm để lấy kết quả ngày tháng và thời gian hiện tại, sau đó trừ đi ngày và thời gian bắt đầu.
Giả sử ô A2 chứa thời điểm bắt đầu, công thức cho kết quả sau với cột B được định dạng thời gian phù hợp, trong ví dụ này nó có dạng
Trong trường hợp khoảng thời gian trôi qua vượt quá 24 giờ, sử dụng một trong các định dạng thời gian sau, ví dụ d ‘days’ h:mm:ss như trong hình sau:
=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) – A2
Công thức 6. Hiển thị chênh lệch thời gian “XX days, XX hours, XX minutes and XX seconds”
Bạn sử dụng các để lấy kết quả thời gian tương tứng và hàm để tính chênh lệch ngày.Sau đó, bạn lồng ghép các hàm vào cùng một công thức cùng các chuỗi text như sau :
Khi tính toán chênhh lệch thờ gian, bạn có thể bắt gặp lỗi ##### vì độ chênh lệch này âm. Tuy nhiên, cũng có nhiều cách để hiển thị kết quả âm trong Excel.
Nếu không muốn sử dụng phương pháp 1, bạn có thể sử dụng công thức sau:
Cộng/trừ thời gian trong Excel
Về cơ bản, có 2 cách cộng/trừ thời gian trong Excel:
Sử dụng hàm TIME
Sử dụng phép toán dựa trên số giờ (24), số phút (1440), số giây (86400) trong một ngày.
Tham khảo ngay: Giáo trình Excel 2020
Cộng/trừ giờ vào thời gian cụ thể
Hàm thời gian để cộng thêm dưới 24 giờ =Start time + TIME(N hours, 0, 0)
Ví dụ, trong ô A2 là thời gian bắt đầu, bạn muốn thêm 2 giờ vào đó, công thức sẽ là:
=A2 + TIME(2, 0, 0)
Chú ý. Nếu bạn thử cộng hơn 23 giờ bằng hàm số giờ được chỉ định sẽ chia cho 24 và phần nguyên sẽ thêm vào thời gian bắt đầu. Ví dụ, nếu bạn cộng thêm 25 giờ vào 6/2/20155 10:00 AM (ô A4) bằng công thức =A4 + TIME(25, 0, 0), kết quả sẽ là: 06/02/2015 11:00, cụ thể là A4 + 1 giờ
Công thức để cộng bất kỳ số giờ nào (dưới/trên 24 giờ)
Công thức sau không có giới hạn về số giờ bạn muốn cộng thêm:
=Start time + (N hours/24)
Ví dụ, để cộng thêm 28 giờ vào ô A2, nhập công thức sau:
Ví dụ, trừ ô A2 cho 3 giờ, nhập một trong các công thức sau:
=A2-(3/24) =A2-TIME(3,0,0)
Nếu bạn trừ số giờ lớn hơn 23, sử dụng công thức đầu tiên.
Cách cộng/trừ phút vào thời gian cụ thể
Cộng/trừ dưới 60 phút
Sử dụng hàm TIME để cộng trừ số phút bạn muốn vào tham số thứ 2:
=Start time + TIME(0, N minutes, 0)
Đây là một vài công thức thực tế để tính số phút trong Excel:
Công 20 phút vào A2: =A2 + TIME(0,20,0)
Trừ A2 cho 30 phút: =A2 – TIME(0,30,0)
Cộng trừ trên 60 phút
Trong phép tính của bạn, chia số phút cho 1440 – số phút trong một ngày và thêm thời gian bắt đầu vào :
=Start time + (N minutes / 1440)
Để trừ số phút, thay dấu + thành dấu -. Ví dụ :
Cộng thêm 200 phút: =A2 + (200/1440)
Trừ đi 300 phút: =A2 -(300/1440)
Để cộng thêm số giây ít hơn 60 vào thời gian cụ thể, sử dụng hàm TIME:
=Start time + TIME(0, 0, N seconds)
Để cộng thêm số giây lớn hơn 59, sử dụng công thức:
=Start time + (N seconds / 86400)
Để trừ số giây, tận dụng công thức tương tự nhưng thay dấu + thành dấu -.
Trên trang tính Excel, công thức trông như sau:
Phép cộng Excel thường sử dụng hàm SUM, và áp dụng định dạng phù hợp cho kết quả.
Giả sử bạn có một vài thời gian trong cột B và bạn muốn cộng chúng với nhau. Bạn viết công thức như sau , kết quả hiển thị theo định dạng mặc định như
Trong một vài trường hợp, định dạng thời gian mặc định có thể ổn, nhưng đôi khi bạn muốn hiển thị tổng thời gian dưới dạng phút và giây, hoặc chỉ hiển thị giây. Khi đó, bạn không cần phép tính nào cả, bạn chỉ cần định dạng lại thời gian cho ô tính chứa công thức SUM.
Nhấp phải vào ô tính và chọn Format Cells từ danh sách tùy chọn, nhấn để mở hộp thoại Format Cells. Chọn từ Category list và gõ một trong các định dạng sau vào hộp :
Hiển thị thời gian dưới dạng phút và giây: [m]:ss
Hiển thị thời gian dưới dạng giây: [ss]
Kết quả như sau:
=SUM($B$2:$B$4)
Chú ý. Định dạng thời gian chỉ áp dụng cho số dương. Nếu kết quả tính toán là số âm, nghĩa là bạn lấy giá trị nhỏ hơn trừ cho giá trị lớn hơn, kết quả sẽ hiển thị .
Cách Tính Chênh Lệch Thời Gian Trong Excel
CÔNG THỨC 1. TRỪ THỜI GIAN
Như bạn đã biết, thời gian trong Excel thường được định dạng số thập phân để trông giống thời gian. Vì chúng là số, nên bạn có thể cộng trừ thời gian như các giá trị số khác.
Công thức đơn giản nhất để tính chênh lệch thời gian là:
=End time – Start time
Tùy vào cấu trúc dữ liệu, chênh lệch thời gian thực tế có thể có nhiều dạng khác nhau, ví dụ:
=A2-B2Tính toán chênh lệch thời gian giữa hai giá trị trong ô A2 và B2
=TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”)Tính toán chênh lệch thời gian giữa hai thời gian cụ thể
=TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) – TIME(HOUR(B2), MINUTE(B2), SECOND(B2))Tính toán chênh lệch thời gian giữa hai giá trị trong ô A2 và B2, không tính chênh lệch ngày tháng nếu ô tính chứa cả giá trị ngày tháng và thời gian
Trong hệ thống Excel, thời gian biểu thị ở phần thập phân, bạn sẽ có kết quả như sau:
Phần thập phân trong cột D đúng nhưng không có ý nghĩa. Để giúp chúng cung cấp thông tin có ích hơn, bạn có thể áp dụng định dạng thời gian với một trong các mã sau đây:
Để áp dụng định dạng thời gian, nhấn để mở hộp thoại Format Cells, chọn từ Category list và gõ mã thời gian vào hộp .
Bây giờ, để xem công thức tính chênh lệch thời gian và mã thời gian hoạt động như thế nào trong bảng tính thực sự. Với Start time trong cột A và End time trong cột B, bạn có thể sao chép công thức vào cột C và E:
=$B2-$A2
Thời gian chênh lệch được hiển thị khác nhau tùy vào định dạng thời gian trong mỗi cột:
Chú ý. Nếu thời gian chênh lệch được hiển thị thành , nghĩa là ô chứa công thức không đủ rộng để hiển thị hết thời gian hoặc kết quả phép tính là một giá trị âm.
CÔNG THỨC 2. TÍNH CHÊNH LỆCH THỜI GIAN BẰNG HÀM TEXT
Một kỹ thuật khác để tính chênh lệch thời gian trong Excel là sử dụng hàm TEXT .
Tính toán chênh lệch giờ giữa hai thời gian: =TEXT(B2-A2, “h”)
Tính toán chênh lệch giờ và phút giữa hai thời gian: =TEXT(B2-A2, “h:mm”)
Tính toán chênh lệch giờ, phút và giây giữa hai thời gian: =TEXT(B2-A2, “h:mm:ss”)
Chú ý.
Kết quả của hàm TEXT luôn là văn bản. Các giá trị text được căn lề trài trong các cột C, D, E trong hình trên. Trong vài tình huống nhất định, điều này có thể là một hạn chế vì bạn sẽ không thể sử dụng kết quả này để tiếp tục tính toán.
Nếu kết quả là một số âm, công thức TEXT sẽ hiển thị lỗi #VALUE!.
CÔNG THỨC 3. TÍNH GIỜ, PHÚT, GIÂY GIỮA HAI THỜI GIAN
Để hiển thị chênh lệch giữa hai thời gian bằng một đơn vị (giờ, phút, giây), bạn cần thực hiện các phép tính sau.
Phép tính giờ giữa hai thời gian =(End time – Start time) * 24
Giả sử thời gian bắt đầu trong ô A2 và thời gian kết thúc trong ô B2, bạn hãy sử dụng phép tính đơn giản là B2-A2 để tính toán chênh lệch thời gian, sau đó nhân với 24.
=(B2-A2) * 24
Để có số Complete hours, sử dụng hàm INT để làm tròn đến số nguyên hần nhất:
=INT((B2-A2) * 24) Tổng số phút giữa hai thời gian:
Để tính số phút giữa hai thời gian, nhân chênh lệch thời gian với 1440
=(End time – Start time) * 1440
Công thức có thể cho kết quả âm hoặc dương, kết quả âm xảy ra khi thời gian kết thúc lớn hơn thời gian bắt đầu như hàng 5:
=(B2-A2)*1440 Tổng số giây giữa hai thời gian:
Để tính số giây giữa hai thời gian, nhân chênh lệch thời gian với 86400
=(End time – Start time) * 86400
Trong ví dụ, công thức sẽ như sau:
=(B2-A2)* 86400
Chú ý. Định dạng nên được thiết lập cho ô tính chứa công thức tính chênh lệch thời gian để kết quả hiển thị đúng.
CÔNG THỨC 4. TÍNH CHÊNH LỆCH THỜI GIAN TRONG CÙNG MỘT ĐƠN VỊ THỜI GIAN VÀ BỎ QUA CÁC ĐƠN VỊ KHÁC
Để tính chênh lệch giữa hai thời gian trong cùng một đơn vị, bỏ qua các đơn vị thời gian khác, hãy sử dụng một trong các hàm sau:
Chênh lệch giờ, bỏ qua phút và giây:
=HOUR(B2-A2)
Chênh lệch phút, bỏ qua giờ và giây:
=MINUTE(B2-A2)
Chênh lệch giây, bỏ qua giờ và phút:
=SECOND(B2-A2)
Khi sử dụng hàm HOUR, MINUTE, SECOND, hãy nhớ rằng kết quả không được vượt quá 24 giờ, 60 phút hoặc 60 giây.
Chú ý. Nếu thời gian kết thúc lớn hơn thời gian bắt đầu, cụ thể nếu kết quả là số âm, công thức sẽ hiển thị lỗi
CÔNG THỨC 5. TÍNH THỜI GIAN TRÔI QUA KỂ TỪ THỜI GIAN BẮT ĐẦU ĐẾN BÂY GIỜ
Để tính thời gian đã trôi qua, bạn có thể sử dụng hàm Now và Time. Trường hợp khoảng thời gian trôi qua dưới 24 giờ, bạn chỉ cần sử dụng hàm NOW để lấy kết quả ngày tháng và thời gian hiện tại, sau đó trừ đi ngày và thời gian bắt đầu.
Giả sử ô A2 chứa thời điểm bắt đầu, công thức cho kết quả sau với cột B được định dạng thời gian phù hợp, trong ví dụ này nó có dạng
Trong trường hợp khoảng thời gian trôi qua vượt quá 24 giờ, sử dụng một trong các định dạng thời gian sau, ví dụ d ‘days’ h:mm:ss như trong hình sau:
Nếu thời gian bắt đầu chỉ có giờ phút mà không có ngày, bạn cần sử dụng hàm Time để tính toán khoảng thời gian trôi đi một cách chính xác. Ví dụ, công thức sau trả về khoảng thời gian từ ô A2 đến giờ.
=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) – A2
Chú ý. Khoảng thời gian trôi qua sẽ không được cập nhật theo thời gian thực tế, nó chỉ cập nhật khi bảng tính được mở lại hoặc tính toán lại. Để cập nhật công thức, nhấn để tính lại trang tính hoặc nhấn để tính lại toàn bộ bảng tính.
CÔNG THỨC 6. HIỂN THỊ CHÊNH LỆCH THỜI GIAN “XX DAYS, XX HOURS, XX MINUTES AND XX SECONDS”
Bạn sử dụng các hàm HOUR, MINUTE và để lấy kết quả thời gian tương tứng và hàm để tính chênh lệch ngày.Sau đó, bạn lồng ghép các hàm vào cùng một công thức cùng các chuỗi text như sau :
=INT(B2-A2) & ” days, ” & HOUR(B2-A2) & ” hours, ” & MINUTE(B2-A2) & ” minutes and ” & SECOND(B2-A2) & ” seconds”
Để ẩn giá trị 0, lồng bốn hàm IF vào đó:
d “days,” h “hours,” m “minutes and” s “seconds”
Cách tính và hiển thị thời gian âm trong excel
Khi tính toán chênhh lệch thờ gian, bạn có thể bắt gặp lỗi ##### vì độ chênh lệch này âm. Tuy nhiên, cũng có nhiều cách để hiển thị kết quả âm trong Excel.
Nhấp OK để lưu cài đặt mới, giá trị âm sẽ được hiển thị như sau:
PHƯƠNG PHÁP 2. TÍNH TOÁN THỜI GIAN ÂM BẰNG CÔNG THỨC
Nếu không muốn sử dụng phương pháp 1, bạn có thể sử dụng công thức sau:
Chú ý. Hãy nhớ rằng phương pháp đầu tiên cho ra kết quả âm là giá trị số âm, kết quả hàm TEXT luôn là chuỗi văn bản và không thể sử dụng trong phép toán hoặc công thức khác.
Cộng/trừ thời gian trong excel
Về cơ bản, có 2 cách cộng/trừ thời gian trong Excel:
Sử dụng hàm TIME
Sử dụng phép toán dựa trên số giờ (24), số phút (1440), số giây (86400) trong một ngày.
CỘNG/TRỪ GIỜ VÀO THỜI GIAN CỤ THỂ
Hàm thời gian để cộng thêm dưới 24 giờ =Start time + TIME(N hours, 0, 0)
Ví dụ, trong ô A2 là thời gian bắt đầu, bạn muốn thêm 2 giờ vào đó, công thức sẽ là:
=A2 + TIME(2, 0, 0)
Chú ý. Nếu bạn thử cộng hơn 23 giờ bằng hàm số giờ được chỉ định sẽ chia cho 24 và phần nguyên sẽ thêm vào thời gian bắt đầu. Ví dụ, nếu bạn cộng thêm 25 giờ vào 6/2/20155 10:00 AM (ô A4) bằng công thức =A4 + TIME(25, 0, 0), kết quả sẽ là: 06/02/2015 11:00, cụ thể là A4 + 1 giờ
CÔNG THỨC ĐỂ CỘNG BẤT KỲ SỐ GIỜ NÀO (DƯỚI/TRÊN 24 GIỜ)
Công thức sau không có giới hạn về số giờ bạn muốn cộng thêm:
=Start time + (N hours/24)
Ví dụ, để cộng thêm 28 giờ vào ô A2, nhập công thức sau:
=A2 + (28/24)
Để trừ đi một số giờ, sử dụng công thức tương tự như thay dấu + bằng dấu –
Ví dụ, trừ ô A2 cho 3 giờ, nhập một trong các công thức sau:
=A2-(3/24) =A2-TIME(3,0,0)
Nếu bạn trừ số giờ lớn hơn 23, sử dụng công thức đầu tiên.
CÁCH CỘNG/TRỪ PHÚT VÀO THỜI GIAN CỤ THỂ
Cộng/trừ dưới 60 phút
Sử dụng hàm TIME để cộng trừ số phút bạn muốn vào tham số thứ 2:
=Start time + TIME(0, N minutes, 0)
Đây là một vài công thức thực tế để tính số phút trong Excel:
Công 20 phút vào A2: =A2 + TIME(0,20,0)
Trừ A2 cho 30 phút: =A2 – TIME(0,30,0)
Cộng trừ trên 60 phút
Trong phép tính của bạn, chia số phút cho 1440 – số phút trong một ngày và thêm thời gian bắt đầu vào :
=Start time + (N minutes / 1440)
Để trừ số phút, thay dấu + thành dấu -. Ví dụ :
Cộng thêm 200 phút: =A2 + (200/1440)
Trừ đi 300 phút: =A2 -(300/1440)
CÁCH CỘNG/TRỪ GIÂY VÀO THỜI GIAN CỤ THỂ
Để cộng thêm số giây ít hơn 60 vào thời gian cụ thể, sử dụng hàm TIME:
=Start time + TIME(0, 0, N seconds)
Để cộng thêm số giây lớn hơn 59, sử dụng công thức:
=Start time + (N seconds / 86400)
Để trừ số giây, tận dụng công thức tương tự nhưng thay dấu + thành dấu -.
Trên trang tính Excel, công thức trông như sau:
Ứng Dụng Excel Trong Việc Tính Giá Trị Tiền Theo Thời Gian
1. Giá trị tương lai của một khoản tiền
Ví dụ 1: Bạn gửi ngân hàng một khoản tiền 100 triệu, lãi suất 7.5%/năm, thời hạn gửi 3 năm. Tính số tiền cả gốc và lãi vào cuối năm thứ 3. Tính theo lãi đơn (lựa chọn 2 trong ảnh trên): tổng số tiền = 100 + 100 * 7.5% * 3 = 122.5 triệu. Tính theo lãi ghép (lựa chọn 1 trong ảnh trên): tổng số tiền = 100 * (1+0.075)^3 〜 124.23 triệu.
Công thức toán học tổng quát cho tính lãi ghép:
FV(n) = FV(n-1)* (1+i) = FV(n-2)*(1+i)^2 =...=PV*(1+i)^n PV: giá trị hiện tại của một khoản tiền n: số kỳ tính lãi i: lãi suất theo kỳ FV: giá trị tương lai của một khoản tiền FV(rate,nper,pmt,[pv],[type]) FV: giá trị của tiền trong tương lai rate: lãi suất nper: số kỳ pmt: dòng tiền đều (phần này tôi sẽ giải thích ở mục 3) pv: giá trị của tiền hiện tại (mang giá trị âm) type: thời điểm thanh toán đầu kỳ hoặc cuối kỳ. Giá trị mặc định type = 0, là thanh toán cuối kỳ.Với ví dụ 1 ở trên, tổng số tiền gốc và lãi cuối năm thứ 3 = fv(0.075, 3,-100,0) = 124.23 triệu. Hết sức đơn giản! Giải thích chi tiết hơn về hàm FV, có thể tham khảo tại link:
2. Giá trị hiện tại của một khoản tiền
Ở trên, chúng ta đã tính giá trị của một khoản tiền trong tương lai. Ngược lại, chúng ta có bài toán tính số tiền cần gửi vào ngân hàng vào thời điểm hiện tại để đạt được 1 khoản tiền X nào đó trong tương lai. Theo công thức toán học ở mục 1, chúng ta sẽ có công thức:
PV = FV/(1+i)^n PV(rate, nper,pmt,fv,type)Ví dụ 2: Nếu bạn muốn có một khoản tiền 2 tỷ (2000 triệu) vào 5 năm sau, giả sử lãi suất ngân hàng ổn định ở mức 7.5%/năm, thì ngay bây giờ bạn phải gửi vào ngân hàng bao nhiêu tiền với kỳ hạn gửi 1 năm, chuyển cả gốc và lãi vào cuối kỳ.
PV = pv(0.075, 5, 0, -2000,0)=1393.12 triệu.3. Giá trị tương lai của dòng tiền đều
Ở mục 1., chúng ta đã tính giá trị tương lai của một khoản tiền, nhưng bài toán sẽ trở nên phức tạp hơn trong trường hợp phải tính tổng số tiền gốc và lãi với điều kiện mỗi kỳ chúng ta lại gửi thêm đều đặn 1 khoản tiền đều.
Ví dụ 3: Một người mỗi tháng gửi tiết kiệm 5 triệu đồng trong 30 năm liên tiếp. Giả sử lãi suất ổn định là 7.5%/năm, và người đó gửi tiết kiệm với kỳ hạn gửi 1 năm, chuyển gốc và lãi vào cuối kỳ. Trong 30 năm tới thì giá trị khoản tiền của cuối năm thứ 30 là bao nhiêu?
rate = 7.5/12 = 0.625%/tháng nper = 30 * 12 = 360 kỳ pmt =-5 triệu (chính là khoản tiền chúng ta gửi đều đặn từng kỳ) pv = 0 type = 0 Tổng số tiền gốc và lãi sau 30 năm = fv(0.625%, 360,-5,0,0) = 6737.23 triệuVậy là cứ mỗi tháng, bỏ vào tiết kiệm 5 triệu, không tính tới việc trượt giá, hay nhà nước đổi tiền, thì sau 30 năm chúng ta sẽ có hơn 6.7 tỷ để nghỉ hưu, quả thực là một con số hấp dẫn.
4. Giá trị của dòng tiền đều (pmt)
Ở mục 3, nếu từ năm 30 tuổi, mỗi tháng bạn gửi tiết kiệm 5 triệu, tới năm 60 tuổi, bạn sẽ có 6.7 tỷ để nghỉ hưu.
Ví dụ 4: Giả thiết rằng, bạn sống được tới 90 tuổi, thì mỗi tháng bạn được rút bao nhiêu tiền vào mỗi đầu tháng?
Excel cung cấp cho chúng ta hàm pmt(rate, nper, pv, fv, type). Tham khảo tại link. Trong bài toán này, chúng ta có:
rate = 0.625% nper=12 * 30 = 360 kỳ pv = 6737.23 triệu fv=0 type = 1 (do tiền được rút ra vào đầu kỳ) Số tiền được rút ra mỗi đầu tháng = pmt(0.625%,360,6737.23,0,1)= 46.82 triệu.5. Kết luận
Với những công cụ đơn giản trên, chúng ta có thể tính được một số bài toán đơn giản như tiết kiệm để mua nhà, để nghỉ hưu hoặc mua bảo hiểm nhân thọ. Các bạn cũng có thể download file excel đã dùng trong các ví dụ trên ở đây, hoặc file google sheet ở đây.
Ở phần tiếp theo, tôi sẽ viết về cách áp dụng công thức tính pmt trong bài toán mua nhà với dư nợ gốc giảm dần theo kỳ.
Hướng Dẫn Cách Lập Công Thức Tính Thời Gian Hoàn Vốn Của Dự Án Trong Excel
Thời gian hoàn vốn của dự án (tiếng anh là Payback Period = PP) là thời gian cần thiết để thu hồi đủ số vốn đầu tư ban đầu của dự án. Đây là một trong những tiêu chí dùng để đánh giá hiệu quả hoạt động của 1 dự án. Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn cách lập công thức tính thời gian hoàn vốn của dự án trong Excel.
Cách tính thời gian hoàn vốn
Khi đánh giá, tính toán thời gian hoàn vốn của 1 dự án, chúng ta thường thấy có 2 trường hợp:
Trường hợp 1: Thu nhập do đầu tư mang lại bằng nhau theo các năm
Trong trường hợp này công thức tính thời gian hoàn vốn như sau:
Thu nhập ròng 1 năm = Khấu hao 1 năm + Lợi nhuận sau thuế 1 năm
Trường hợp 2: Thu nhập do khoản đầu tư mang lại không bằng nhau theo các năm
Bước 1: Xác định ngân lưu ròng (lợi nhuận ròng) của từng năm = Doanh thu từng năm – Chi phí từng năm
Bước 2: Xác định ngân lưu ròng tích lũy theo từng năm = Vốn đầu tư ban đầu (số âm) + ngân lưu ròng từng năm
Tại thời điểm ngân lưu ròng tích lũy = 0 là thời điểm hòa vốn.
Thời gian hoàn vốn = Thời gian từ khi dự án bắt đầu đầu tư cho tới thời điểm hòa vốn.
Công thức tính thời gian hoàn vốn của dự án trong Excel
Hãy xét ví dụ sau đây:
Dự án A có mức đầu tư như sau:
Ban đầu đầu tư vào dự án 100 triệu
Năm đầu tiên hoạt động, doanh thu là 50 triệu
Năm thứ 2 doanh thu đạt 40 triệu
Năm thứ 3 doanh thu đạt 30 triệu
Năm thứ 4 doanh thu đạt 20 triệu
Năm thứ 5 doanh thu đạt 10 triệu
Hỏi thời gian hoàn vốn là bao nhiêu năm?
Khi biểu diễn các thông tin này trên Excel ta có:
Bước 1: Tính ngân lưu ròng theo các năm
= Doanh thu – Vốn đầu tư
C5=C4-C3
…
G5=G4-G3
Năm thứ 1: C6=B6+C5 (Lấy ngân lưu ròng tích lũy năm trước cộng với Ngân lưu ròng năm sau)
Năm thứ 2: D6=C6+D5
…
Năm thứ 5: G6=F6+G5
Số tháng trong năm thứ 3 để đạt được 10 triệu là:
=(10/30)*12 = 4 tháng
Tổng thời gian hoàn vốn là: 2 năm + 4 tháng
Ngoài ra để đánh giá đầy đủ về 1 dự án, chúng ta cần xét thêm các thông số khác về dự án như NPV và IRR. Các bạn có thể tìm hiểu tại bài viết:
IRR là gì – Hướng dẫn cách dùng và ý nghĩa của hàm IRR trong Excel Hướng dẫn tính lợi suất dòng tiền không định kỳ bằng hàm tài chính XIRR
Bạn đang đọc nội dung bài viết Cách Tính Thời Gian Trong Excel trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!